| Subject | Re: [ib-support] select etc. |
|---|---|
| Author | Arno Brinkman |
| Post date | 2003-03-18T12:19:43Z |
Hi,
optimizer in some cases was not choosing the right PLANs to retrieve these
sub-queries. In FB1.5 this is much better. Just try it in FB1.0 and look at
the PLANs. If an index is available on MYTABLE2.MYFIELD1 the PLAN should
look like :
PLAN (MYTABLE2 INDEX (RDB$SOME_INDEX))
PLAN (MYTABLE1 NATURAL)
The EXISTS solution should have exactly the same PLAN.
Arno
> When did this change happen?This was already the case for FB1.0 / IB6.0, but it can be that the
optimizer in some cases was not choosing the right PLANs to retrieve these
sub-queries. In FB1.5 this is much better. Just try it in FB1.0 and look at
the PLANs. If an index is available on MYTABLE2.MYFIELD1 the PLAN should
look like :
PLAN (MYTABLE2 INDEX (RDB$SOME_INDEX))
PLAN (MYTABLE1 NATURAL)
The EXISTS solution should have exactly the same PLAN.
> >Internally the IN is converted to an sub-query which will look like this:
> >Regards,
> >SELECT MYFIELD1 FROM MYTABLE2 WHERE MYTABLE2.MYFIELD1 = MYTABLE1.MYFIELD1
> >
> >So i don't expect any difference in speed here between IN and EXISTS.
Arno