> > Thanks Dmitry, I can piece that together now... And as we (should?)
> > suspect, though, while FB1,5 accepts this: FROM ERSCONTRACTOR
> > WHERE erscontractor.id not in
> > (SELECT FKCONTRACTOR FROM ERSCONTBANS WHERE FKCLIENTSITE IN
> (SELECT ID FROM
> > ERSCLIENTSITES WHERE ERSCLIENTSITES.FKCLIENT=291 PLAN
> (ERSCLIENTSITES INDEX
> > (PK_ERSCLIENTSITES))) PLAN (ERSCONTBANS INDEX (FK_ERSCONTBANS)))
> > PLAN (ERSCONTRACTOR NATURAL)
> > FB2 objects to the
> > PLAN (ERSCONTBANS INDEX (FK_ERSCONTBANS))
> > And says:
> > index cannot be used in the specified plan.
> > index FK_ERSCONTBANS cannot be used in the specified plan.
> > It uses natural instead.
> >
> > Is this a function of the NOT IN predicate?
>
> NOT IN cannot use indices in v2.0, this is a known (by
> design) limitation.
>
> If neither erscontractor.id nor ERSCONTBANS.FKCONTRACTOR are
> nullable,
> then try NOT EXISTS instead, it should make you happy again.
>
> Dmitry

Quite..
Changing this
WHERE erscontractor.id not in
(SELECT FKCONTRACTOR FROM ERSCONTBANS WHERE FKCLIENTSITE IN (SELECT ID FROM
ERSCLIENTSITES WHERE ERSCLIENTSITES.FKCLIENT=291))
To this...
WHERE not exists
(SELECT FKCONTRACTOR FROM ERSCONTBANS WHERE FKCLIENTSITE IN (SELECT ID FROM
ERSCLIENTSITES WHERE ERSCLIENTSITES.FKCLIENT=291) and
fkcontractor=erscontractor.id)

Turns a 20 second query (was 375ms n FB1.5) into a 500ms query in FB2.
I knew this would be the case, my original post was cetnred around
understanding the plan syntax and I undertand it quite well now - thanks
again.
Alan