Subject Forcing optimizer to use index
Author Rick Debay
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.