At 09:59 AM 8/04/2008, you wrote:

>I am upgrading to FB2
>A query in FB1.5 which performs very well returns this plan (in
>IBExpert)
>PLAN (ERSCLIENTSITES INDEX (PK_ERSCLIENTSITES))
>PLAN (ERSCONTBANS INDEX (FK_ERSCONTBANS))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX2,ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX1,ERSJOB_IDX4))
>PLAN (ERSCONTRACTORALERTS INDEX (FK_ERSCONTRACTORALERTS))
>PLAN (ERSCONTRACTOR NATURAL)
>
>but now in FB2 it returns
>PLAN (ERSCLIENTSITES INDEX (PK_ERSCLIENTSITES))
>PLAN (ERSCONTBANS INDEX (FK_ERSCONTBANS))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4))
>PLAN (ERSJOB INDEX (ERSJOB_IDX4,ERSJOB_IDX2)) <-- different order
>PLAN (ERSJOB INDEX (ERSJOB_IDX4,ERSJOB_IDX1)) <-- different order
>PLAN (ERSCONTRACTORALERTS INDEX (FK_ERSCONTRACTORALERTS))
>PLAN (ERSCONTRACTOR NATURAL)
>
>and it's very slow...
>How can I incorporate (force) the desired plan (first one) into the
>query statement?

You can't "force" the optimizer to use an index where it can't use an index. Fb 1.5 allowed an index to be used for a NOT IN() query, Fb 2 and above don't.

>The release notes do not have a comprehensive set of examples nor can I find any.

Examples of predicates that don't use indexes? Hmmm, isn't that a bit like a directory of people who don't have telephone numbers?

Does a NOT EXISTS() predicate improve things at all? Or, failing that, a left outer join with a corresponding "where outertable.xxx is null" ?

./heLen