> I have a query that needs to use an index that the optimizer isn't
> using. How can I get it to add the index to the query plan?
> The current plan is
>
> PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
> (I_PBM_CLAIM_NDC), AC RGM SA INDEX (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C
> INDEX (PK_CHC), AC RGM PRG INDEX (PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX
> (PK_CRM_RPL_GRP)))
>
> And it needs to be
>
> PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
> (I_PBM_CLAIM_NDC, I_PBMCLAIM_DATESBM), AC RGM SA INDEX
> (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX
> (PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP)))
>
> Without I_PBMCLAIM_DATESBM instead of scanning a few months of data it's
> scanning a few years.

Keep in mind and make sure, that:

* Your statistics are up-to date before fooling the optimizer with a
fixed plan and/or hacks to dismiss certain indexes

* You test in a fetch all rows scenario. While a certain index might
make execution of a statement look fast when fetching the first bunch of
rows automatically due to a client request, things may look different
compared to non-indexed access when you fetch all records

Other than that, you are in luck when Set picks up such questions. ;-)


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/