Alan McDonald wrote:

>
> 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