2.0 plan is forty times slower than 1.5 plan.

Would this be a case where it's a good idea to have compound keys? I
think the problem is that 2.0 doesn't know that combining two moderately
selective indexes results in good selectivity in this case.

Scratch that, I combined the two indexes as a test, and 2.0 kept the
original plan. If the 2.0 optimizer is still being tuned I can upload a
sanitized version of the DB.

1.5 Plan: DP uses unique index U_RXS_DRUG_PRICE. MD_OLD uses
I_MDDBDRUG_GPI and I_MDDB_DRUG_CORE9 which when used together are
relatively selective (about a dozen items on the average). Query time
is less than 0.05 seconds.

PLAN JOIN (NC MD_OLD INDEX (I_MDDBDRUG_GPI,I_MDDB_DRUG_CORE9),NC NC
INDEX (I_MDDB_NDC_CHANGE_OLDNDC),NC MD_NEW INDEX (PK_MDDB_DRUG),DP DC
INDEX (PK_RPL_WHLSLR_DIST_CTR),DP DP INDEX (U_RXS_DRUG_PRICE))

2.0 Plan: DP uses foreign key FK_DRUGPRICE_WHLSLRDISTCTR, which has
poor selectivity (a distribution center stocks about sixty thousand
items). MD_OLD now uses PK_MDDB_DRUG. Query time is two seconds

PLAN JOIN (DP DC INDEX (PK_RPL_WHLSLR_DIST_CTR), DP DP INDEX
(FK_DRUGPRICE_WHLSLRDISTCTR, FK_DRUGPRICE_WHLSLRDISTCTR), NC NC INDEX
(I_MDDB_NDC_CHANGE_NEWNDC), NC MD_NEW INDEX (PK_MDDB_DRUG), NC MD_OLD
INDEX (PK_MDDB_DRUG))

Modified 2.0 Plan: DP now uses U_RXS_DRUG_PRICE, but for some reason DC
now uses an additional foreign key. Query time is less than 0.05
seconds.

PLAN JOIN (NC MD_OLD INDEX (I_MDDB_DRUG_CORE9, I_MDDBDRUG_GPI), NC NC
INDEX (I_MDDB_NDC_CHANGE_OLDNDC), NC MD_NEW INDEX (PK_MDDB_DRUG), DP DP
INDEX (U_RXS_DRUG_PRICE), DP DC INDEX (PK_RPL_WHLSLR_DIST_CTR,
FK_DISTCTR_WHLSLR, FK_DISTCTR_WHLSLR))

Query generating above plans:

SELECT
*
FROM
V_MDDB_NDC_CHANGE nc
JOIN V_RXS_DRUG_PRICE dp
ON dp.NDC = nc.NEW_PRODUCTID
WHERE
dp.ACTIVE_FLAG = 'Y' AND
/* modification to 2.0 query to alter plan */
dp.DISTRB_CTR+0 = ? AND
nc.OLD_PID_CORE9 = ? AND
nc.OLD_GPI = ? AND
nc.OLD_TEE = ? AND
nc.OLD_UNIT_DOSE_USE = ? AND
(nc.OLD_ATOM <> 'Y' OR nc.OLD_PKG_SZ = ?) AND
((nc.OLD_ATOM = 'Y' AND nc.NEW_ATOM = 'Y' AND nc.NEW_QTY <=
?*nc.NEW_PKG_SZ/nc.OLD_PKG_SZ) OR
((nc.OLD_ATOM <> 'Y' OR nc.NEW_ATOM <> 'Y') AND nc.NEW_QTY <=?))

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.