> 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/
