Are any of the developers willing to take a look at why the 2.0
optimizer hates our database?
It seems many of our queries run fine in 1.5, but require tweaks to get
the 2.0 to choose a rational plan.
Aside from my previous posts on the subject, I just hit this, and since
it's a view there is no way that I can tweak the query to force it to
generate a decent plan.

SELECT
CHC,PHARMACY,GROUPID,-SUM(PHARM_COLLECT)
FROM
V_SUBACCT_CLAIMFEES
WHERE
GRANTEE_INVOICE = ?
GROUP BY
CHC,PHARMACY,GROUPID

2.0 takes 8.8 seconds, 1.5 takes 0.11 seconds.

2.0
PLAN SORT (JOIN (V_SUBACCT_CLAIMFEES CP NATURAL, V_SUBACCT_CLAIMFEES CPR
INDEX (FK_PBMCLAIM_ACTCHCPHARMSUBACCT), V_SUBACCT_CLAIMFEES CCF INDEX
(PK_RXSCLMCALCFEES)))
1.5
PLAN SORT (JOIN (V_SUBACCT_CLAIMFEES CCF INDEX
(FK_CCF_GRANTEEINVOICE),V_SUBACCT_CLAIMFEES CPR INDEX
(PK_PBMCLAIM),V_SUBACCT_CLAIMFEES CP INDEX (PK_ACT_CHC_PHARM_SUBACCT)))

The view is

SELECT
ccf.CLAIM_ID,
cp.SUBACCT, cp.PHARMACY, cp.CHC, cpr.GROUPID,
ccf.DISPENSEFEE, ccf.PHARMACYCOLLECTED, ccf.CHCAMOUNT, ccf.RXSFEE,
ccf.CHCFEE,
cpr.CALINGRCST,
ccf.GRANTEE_INVOICE, ccf.INVC_PHARM_BILLGRP_INVC, ccf.RXS_INVOICE,
ccf.POSTED
FROM
RXS_CLM_CALC_FEES ccf
JOIN PBM_CLAIM cpr
ON ccf.CLAIM_ID = cpr.ID
JOIN ACT_CHC_PHARM_SUBACCT cp
ON cp.PHARMACY = cpr.SRVPROVID AND cp.CHC = cpr.ACCOUNTID

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.