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.
