| Subject | Re: [firebird-support] Re: Firebird 2.1.3 optimizer in does not use index - Email found in subject |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2010-07-20T14:34:41Z |
Andreas Rulle wrote:
to make the explanation even more clear.
English, you've asked for all the values of s1 from t1, then
without any obvious reason, asked Firebird to check for matching
values from T2 - without using them if they exist. The query
returns the exact same values as
select t1.s1 from t1
A very clever optimizer (query re-writer) might recognize that
and just drop the t2 reference completely.
A more rational query of the same form might look like this:
select t1.s1, t2.s2
from t1 left join t2 on t1.s1 = t2.s1
That query still asks for all values to t1.s1 but also wants
the s2 value from any matching t2 records. There is no qualification
on t1, so there's no way to use an index.
The right side of a left outer join is not restricted by the "on"
clause of the outer join, and conversely, the left side of a right
outer join is unrestricted by the "on" clause. The "on" clause of
an inner join (like the one below) can apply to either input source.
select t1.s1, t2.s2
from t1 inner join t2 on t1.s1 = t2.s1
That query has one qualification that can use an index: t1.s1 = t2.s1
Either you get a value for t1.s1 and use it to lookup a record in t2,
or you get a value from t2 and use it to lookup a record in t1. That
query can use only one index, but it could be either the index on t1
or the index on t2. If one index is more selective than the other
(which it can't be in this case) and the tables are approximately the
same size, Firebird will do a natural scan on the table with the less
selective index and use the results to look up values in the other
table using the more selective index.
This query does allow the use of indexes on both tables because there
are two qualifications - t1.s1 = t2.s1 and t1.s1 <= 100
select t1.s1, t2.s2
from t1 left outer join t2 on t1.s1 = t2.s1
where t1.s1 <= 100
Good luck,
Ann
>Several other people have answered this, but I'd like
to make the explanation even more clear.
> create table T1 (s1 integer, s2 varchar(50));Which is reasonable. Translating your query into more or less
> create table T2 (s1 integer, s2 varchar(50));
>
> b) A really small data set ...
>
> c) The indices
>
> create unique index IDX_T11 on T1 (s1);
> create unique index IDX_T12 on T2 (s1);
>
> d) A left join
>
> select t1.s1 from t1 left join t2 on t1.s1=t2.s1
> PLAN JOIN (T1 NATURAL, T2 INDEX (IDX_T12))
> Yes, the optimizer takes the second index, but not the first one.
English, you've asked for all the values of s1 from t1, then
without any obvious reason, asked Firebird to check for matching
values from T2 - without using them if they exist. The query
returns the exact same values as
select t1.s1 from t1
A very clever optimizer (query re-writer) might recognize that
and just drop the t2 reference completely.
A more rational query of the same form might look like this:
select t1.s1, t2.s2
from t1 left join t2 on t1.s1 = t2.s1
That query still asks for all values to t1.s1 but also wants
the s2 value from any matching t2 records. There is no qualification
on t1, so there's no way to use an index.
The right side of a left outer join is not restricted by the "on"
clause of the outer join, and conversely, the left side of a right
outer join is unrestricted by the "on" clause. The "on" clause of
an inner join (like the one below) can apply to either input source.
select t1.s1, t2.s2
from t1 inner join t2 on t1.s1 = t2.s1
That query has one qualification that can use an index: t1.s1 = t2.s1
Either you get a value for t1.s1 and use it to lookup a record in t2,
or you get a value from t2 and use it to lookup a record in t1. That
query can use only one index, but it could be either the index on t1
or the index on t2. If one index is more selective than the other
(which it can't be in this case) and the tables are approximately the
same size, Firebird will do a natural scan on the table with the less
selective index and use the results to look up values in the other
table using the more selective index.
This query does allow the use of indexes on both tables because there
are two qualifications - t1.s1 = t2.s1 and t1.s1 <= 100
select t1.s1, t2.s2
from t1 left outer join t2 on t1.s1 = t2.s1
where t1.s1 <= 100
Good luck,
Ann