I ran a select statement that took 18.552 seconds to return 45 rows. A
view created from the same select statement takes 669.411 seconds to
retrieve 45 rows.
When I prepare them they both return the same plan, except for one item.
I ran the select statement first, so that should have loaded the cache
and the view should have taken less time.
The problem seems to be that the optimizer isn't applying the
conditional to the select inside the view, instead it's generating a
plan based on the view only. The select statement will generate the
same plan, if it has no additional conditionals.

select plan
PLAN SORT (JOIN (JOIN (A NATURAL,DC INDEX (PK_RPL_WHLSLR_DIST_CTR),W
INDEX (PK_RPL_WHLSLR),PRG INDEX (PK_CRM_PHARM_RPL_GRP),P INDEX
(PK_CRM_PHARM),PO INDEX (FK_RPLPO_CRMWHLSLRACCT),PB INDEX
(FK_POBILL_PURCHORDER),PBI INDEX (FK_POBILLITM_POBILL),D INDEX
(PK_MDDB_DRUG)),CPBI INDEX (FK_RPLCLMPOBILLITM_POBILLITM)))
view plan
PLAN SORT (JOIN (JOIN (R A NATURAL,R DC INDEX (PK_RPL_WHLSLR_DIST_CTR),R
W INDEX (PK_RPL_WHLSLR),R PRG INDEX (FK_PHARMRPLGRP_REPLGRP),R P INDEX
(PK_CRM_PHARM),R PO INDEX (FK_RPLPO_CRMWHLSLRACCT),R PB INDEX
(FK_POBILL_PURCHORDER),R PBI INDEX (FK_POBILLITM_POBILL),R D INDEX
(PK_MDDB_DRUG)),R CPBI INDEX (FK_RPLCLMPOBILLITM_POBILLITM)))

Difference is PRG INDEX (PK_CRM_PHARM_RPL_GRP) vs R PRG INDEX
(FK_PHARMRPLGRP_REPLGRP):

CRM_PHARM_RPL_GRP ADD CONSTRAINT PK_CRM_PHARM_RPL_GRP PRIMARY KEY
(REPLENISH_GRP, PHARMACY)
CRM_PHARM_RPL_GRP ADD CONSTRAINT FK_PHARMRPLGRP_REPLGRP FOREIGN KEY
(REPLENISH_GRP) REFERENCES CRM_RPL_GRP (ID)

This is the select statement that's the same for both queries. When
executed, both the raw select and the view have an additional
conditional appended:

SELECT
Lots of Stuff
FROM
RPL_POBILL_ITM pbi
JOIN MDDB_DRUG d
ON d.PRODUCTID = pbi.NDC
JOIN RPL_POBILL pb
ON pb.ID = pbi.INVOICE
JOIN RPL_PO po
ON po.ID = pb.PO
join CRM_WHLSLR_ACCT a
on a.ID = po.WHLSLR_ACCT
join CRM_PHARM_RPL_GRP prg
on prg.REPLENISH_GRP = a.GRANTEE_REPL_GRP
join CRM_PHARM p
on p.NABPNUM = prg.PHARMACY
join RPL_WHLSLR_DIST_CTR dc
on dc.ID = a.DISTCTR
join RPL_WHLSLR w
on w.ID = dc.WHLSLR
left join RPL_CLM_POBILL_ITM cpbi
on pbi.ID = cpbi.POBILL_ITM
WHERE
/* supress bad index for this query */
po.REPLENISH_GRP<>po.PO AND po.SUBMITTED+0 > '2005-01-01'
GROUP BY
Lots of Stuff
HAVING
pbi.QTY - coalesce(sum(cpbi.QTY_SHIP),0) > 0 AND
d.QTY <= pbi.QTY - coalesce(sum(cpbi.QTY_SHIP),0)

Extra conditional embedded in raw select, and appended to view:

NABPNUM = 1234567


So, is there anything I can do to get the optimizer to fit the plan to
the view plus conditionals? Or am I SOL?


Thanks, Rick DeBay

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.