>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.
Have you timed your query with the plans hardcoded to ascertain that it really is quicker using your preferred plan, Rick? I doubt you'll get much help from this list without including the query itself, the index definitions with a little bit of additional information about their selectivity and tell us which Firebird version you're using. You may be able to reduce the query (and highlight your problem) by eliminating the non-important tuples of your query (you definitely need AC D and AC CA C, but the rest may not matter for your problem), but it might also be that someone will spot a better way to write your query if you include the entire text. One thing regarding your desired plan: I think (don't know, it is just a hunch) that using several indexes for a table is most useful if it is the first table in the plan, so it might be better to have a plan like: PLAN SORT (JOIN (AC CA C INDEX(I_PBMCLAIM_DATESBM, <possibly another index>), AC D INDEX (<index linking to AC CA C>), 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))) Set
