|Subject||RE: [firebird-support] HOWTo PLAN Clause for FB2|
> Alan McDonald wrote:Thanks Dmitry, I can piece that together now... And as we (should?) suspect,
> > 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?
though, while FB1,5 accepts this:
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))
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?