> 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

Maybe
But I'd still like to know the syntax for incorporating a plan (which has
more than one index nominated) in a select statement.
Everything I have tried fails with an error message.
SELECT ...
FROM TABLENAME
WHERE...
ORDER BY...
PLAN (TABLENAME INDEX (ERS_IDX4))
PLAN (TABLENAME INDEX (ERS_IDX2))
etc returns an error at the second PLAN
And
SELECT ...
FROM TABLENAME
WHERE...
ORDER BY...
PLAN (TABLENAME INDEX (ERS_IDX4)) (TABLENAME INDEX (ERS_IDX2))
Returns an error at the second clause
PLAN (TABLENAME INDEX (ERS_IDX4)), (TABLENAME INDEX (ERS_IDX2))
Returns an error with the comma, etc etc
What is the correct syntax?
Alan