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? The release notes do not have a comprehensive set of
examples nor can I find any.

thanks
Alan

I know my query is probably not destined for permanent use in FB2
since it uses the NOT IN clause but I'd still like to know the exact
syntax to force this alternative plan on the query. The query is
SELECT
ID
, CODE
, NAME
, JOBTYPE
, CONTRACTTYPE
, FKCONTRACTTYPE
, CURRENTTENDER
, CRCODE
, GSTREG
, FLAG1
, FLAG2
, FLAG3
, DAILYCHECK
, AREA
, NOTE
, (SELECT COUNT(*) FROM ERSCONTRACTORALERTS WHERE
ERSCONTRACTORALERTS.FKCONTRACTOR=ERSCONTRACTOR.ID) AS ALERTCOUNT
, (SELECT COUNT(*) FROM ERSJOB WHERE
ERSJOB.FKCONTRACTOR=ERSCONTRACTOR.ID AND ERSJOB.COMDATE IS NULL) AS
INCOMPJOBS
, (SELECT COUNT(*) FROM ERSJOB WHERE
ERSJOB.FKCONTRACTOR=ERSCONTRACTOR.ID AND ERSJOB.COMDATE IS NOT NULL
AND ERSJOB.INVOICEDATE IS NULL) AS COMPJOBS
, (SELECT COUNT(*) FROM ERSJOB WHERE
ERSJOB.FKCONTRACTOR=ERSCONTRACTOR.ID AND ERSJOB.COMDATE IS NOT NULL)
TOTALCOMPLETED
, (SELECT COUNT(*) FROM ERSJOB WHERE
ERSJOB.FKCONTRACTOR=ERSCONTRACTOR.ID AND ERSJOB.INVOICEDATE IS NOT
NULL) TOTALINVOICES
, (SELECT COUNT(*) FROM ERSJOB WHERE
ERSJOB.FKCONTRACTOR=ERSCONTRACTOR.ID AND ERSJOB.FINALISED IS NOT
NULL) TOTALFINALISED
FROM ERSCONTRACTOR
WHERE erscontractor.id not in
(SELECT FKCONTRACTOR FROM ERSCONTBANS WHERE FKCLIENTSITE IN (SELECT
ID FROM ERSCLIENTSITES WHERE ERSCLIENTSITES.FKCLIENT=291))