Does anyone want to take a stab at this?

-----Original Message-----
From: Rick DeBay
Sent: Tuesday, August 17, 2004 10:26 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Poor plan with union

If I do the selects independently, indexes are used. If I do a union,
the plan doesn't use an index in part of the query. Is this because
it's doing a full union before trying to resolve the WHERE clause?

First view select and plan;
select * from v_normalclaims where rxclaimnbr=123
PLAN JOIN (V_NORMALCLAIMS V_AUDITEDCLAIMS VC CLAIMSPAIDREVERSED INDEX
(I_CPR_CLMNBR), V_NORMALCLAIMS V_AUDITEDCLAIMS CA INDEX
(FK_CLAIMAUDIT_CPR))

Second view select and plan:
select * from v_correctedclaims where rxclaimnbr=123
PLAN JOIN (V_CORRECTEDCLAIMS VC CLAIMSPAIDREVERSED INDEX
(I_CPR_CLMNBR),V_CORRECTEDCLAIMS CA INDEX (FK_CLAIMAUDIT_CPR),
V_CORRECTEDCLAIMS CC INDEX (PK_CLAIMCORRECTION))

Select from union and its plan:
select * from v_crctd_union_nrml_clm where rxclaimnbr=123
PLAN JOIN (V_CRCTD_UNION_NRML_CLM V_CORRECTEDCLAIMS CC NATURAL,
V_CRCTD_UNION_NRML_CLM V_CORRECTEDCLAIMS CA INDEX (PK_CLAIMAUDIT),
V_CRCTD_UNION_NRML_CLM V_CORRECTEDCLAIMS VC CLAIMSPAIDREVERSED INDEX
(PK_CLAIMSPAIDREVERSED))
PLAN JOIN (V_CRCTD_UNION_NRML_CLM V_NORMALCLAIMS V_AUDITEDCLAIMS VC
CLAIMSPAIDREVERSED NATURAL, V_CRCTD_UNION_NRML_CLM V_NORMALCLAIMS
V_AUDITEDCLAIMS CA INDEX (FK_CLAIMAUDIT_CPR))

View V_NORMALCLAIMS:
SELECT
RXCLAIMNBR,CLMSEQNBR,DATESBM,TIMESBM,RVDATESBM,MULTSRCCDE,PRODUCTID,SRVP
ROVID,SRVPROVNME,PROCESSOR,DAYSSUPPLY,DECIMALQTY,USUALNCUST,LABELNAME,AW
PUNITCST,CLAIMSTS,ACCOUNTID,GROUPID,CTYPEUCOST,CALINGRCST,RSPDUEAMT,RSPP
ATPAY,RSPDISPFEE,AUDITSTATUS
FROM V_AUDITEDCLAIMS
WHERE AUDITSTATUS >= 0 OR AUDITSTATUS IS NULL

View V_CORRECTEDCLAIMS:
SELECT
VC.RXCLAIMNBR,VC.CLMSEQNBR,VC.DATESBM,VC.TIMESBM,VC.RVDATESBM,VC.MULTSRC
CDE,VC.PRODUCTID,VC.SRVPROVID,VC.SRVPROVNME,VC.PROCESSOR,VC.DAYSSUPPLY,V
C.DECIMALQTY,VC.USUALNCUST,VC.LABELNAME,VC.AWPUNITCST,CC.CLAIMSTS,CC.ACC
OUNTID,CC.GROUPID,CC.CTYPEUCOST,CC.CALINGRCST,CC.RSPDUEAMT,CC.RSPPATPAY,
CC.RSPDISPFEE,CA.AUDITSTATUS
FROM V_CLAIMS VC JOIN
(CLAIMAUDIT CA JOIN CLAIMCORRECTION CC ON CA.ID=CC.AUDITID)
ON CA.RXCLAIMNBR=VC.RXCLAIMNBR AND VC.CLMSEQNBR=CA.RXCLAIMSEQ
WHERE VC.CLAIMSTS=CC.CLAIMSTS

View V_CRCTD_UNION_NRML:
SELECT * FROM V_CORRECTEDCLAIMS
UNION
SELECT * FROM V_NORMALCLAIMS

Rick DeBay
Senior Software Developer
RxStrategies.net