Re: selective partition scanning with joins

[Mikael Ronström]

Hi,

niklas Wrote:
-------------------------------------------------------

> The following statment will scan all partitions:
> explain partitions
> select * from t1 left join lookup on
> (t1.a=lookup.b)
> where
> lookup.a=2
>
> whereas the following does not:
>
> explain partitions
> select * from t1 left join lookup on
> (t1.a=lookup.b)
> where
> lookup.a=2
> and lookup.b=3
>
>
> How is this intended to work in the case of joins?
> Is there a way of optmizing this some way?
>

The reason that the second query gets optimised better is because
the join condition can be substituted with t1.a = 3. Not sure
exactly how the optimiser converts this internally but I know that
the first expression has no bounds on the a field in the join
condition.

Rgrds Mikael

> Thanks,
>
> Niklas