Hi,

> > > to quote Celko
> > > "An EXISTS predicate can use indexes to find.. or fail, whereas the IN
> > > predicate ofen has to build the results of the subquery in a working

table"

> >
> > > not sure exactly how FB is optimised in this regard
> >
> >FB 1.5 will use an index if it exists, and also depending on what is
> >in the IN predicate clause. I can't say precisely when an index would
> >be used, because I've always used the EXISTS predicate and never came
> >across a scenario where I had to use the IN predicate.
>
> FB 1.5 actually resolves an IN() predicate to an EXISTS() predicate if it
> can, and then will use an index if a useful one is available. (I thought
> this was one of Arno's 1.5 optimizations, but D. Sibiryakov thinks this
> optimization was in the Fb 1.0.x engine as well..)

a IN predicate is handled internally as EXISTS() if the IN contains a
sub-select else every item will be converted to a OR.
FB1.0 did indeed already use EXISTS() for the IN clause with sub-query. I've
optimized situations where the optimizer didn't use a index at all and some
slowness with aggregate functions as sub-select.

> At this stage, in Firebird, an IN() predicate that doesn't resolve to an
> EXISTS() won't use an index at all. Apparently, Yaffil implemented use of
> an index for all IN() searches...

Except if the OR conditions can be computed to use a index.

SELECT * FROM TableX WHERE PK_KEY IN (1,2,3,4)

should use 4 indices.


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81