> Alan McDonald wrote:
> >
> > PLAN (ERSJOB INDEX (ERSJOB_IDX2,ERSJOB_IDX4))
> > PLAN (ERSJOB INDEX (ERSJOB_IDX1,ERSJOB_IDX4))
> >
> > PLAN (ERSJOB INDEX (ERSJOB_IDX4,ERSJOB_IDX2)) <-- different
> order PLAN
> > (ERSJOB INDEX (ERSJOB_IDX4,ERSJOB_IDX1)) <-- different order
> >
> > and it's very slow...
>
> This is really weird and I would appreciate a test case. It shouldn't
> make any difference what index scan order is chosen. Maybe something
> else is a problem?
>
>
> Dmitry

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?