-
NOT EXISTS replace with NOT IN
Hi
I need to re-write this query with NOT IN statement instead of NOT EXISTS to improve the performance. Please help me.
SELECT DISTINCT
cprdm.prod_id
, cprdm.sty_id
, cprdm.colr_comb_id
, cprdm.colr_dsp_nbr
, cprcmtm.mkt_typ_id
, cprsm.prod_reg_st_cd
, csm.sty_dsp_nbr
, csm.src_sty_id
, csm.uom_cd
, cum.uom_conv_fctr
, cprm.lnch_cd --Relay 164680 & 167034
, cprm.lnch_dt --Relay 164680 & 167034
, cmsm.mkstr_cd -- changes made on 25-Jun-08 DLM
FROM cdb_prod_reg_st cprsm
, cdb_prod_reg_cyc_mkt_typ cprcmtm
, cdb_sty csm
, cdb_prod cprdm
, cdb_org_src_xref cosxm
, cdb_org com
, cdb_org_typ cotm
, cdb_uom cum
, cdb_prod_reg cprm --Relay 164680 & 167034
,cdb_sty_fcst csfm -- changes made on 25-Jun-08 DLM
,cdb_mkt_str cmsm -- changes made on 25-Jun-08 DLM
WHERE cotm.org_typ_desc = 'PRODUCT ENGINE' --k_product_engine
AND com.org_typ_cd = cotm.org_typ_cd
AND cosxm.lob_cd = 'APRL' --k_apparel
AND com.org_id = cosxm.org_id
AND com.org_id = cprdm.init_org_id
AND cprsm.reg_id = 8 --p_reg_id
AND cprsm.reg_id = cprm.reg_id -- changes made on 25-Jun-08 DLM
AND cprsm.prod_id = cprm.prod_id -- changes made on 25-Jun-08 DLM
AND cprsm.prod_id = cprdm.prod_id
AND cprcmtm.prod_id = cprdm.prod_id
AND cprcmtm.reg_id = 8 --p_reg_id
AND cprdm.sty_id = csm.sty_id
AND csm.uom_cd = cum.uom_cd
AND cprsm.sor_fl = 'P' --k_pwb -- diff. vs. USA/CANA/EMEA
and cprcmtm.mkt_typ_id = csfm.mkt_typ_id -- changes made on 25-Jun-08 DLM
and csm.sty_id = csfm.sty_id -- changes made on 25-Jun-08 DLM
and csfm.mkstr_id = cmsm.mkstr_id -- changes made on 25-Jun-08 DLM
AND TRUNC(cprsm.prod_reg_st_bdt)
<= TRUNC(SYSDATE)
AND NVL( TRUNC(cprsm.prod_reg_st_xdt)
, TRUNC(SYSDATE)+1 )
> TRUNC(SYSDATE)
AND NOT EXISTS
( SELECT csm.sty_dsp_nbr
FROM cdb_sty csm2
, cdb_prod cprdm2
, cdb_org_src_xref cosxm2
WHERE cosxm2.lob_cd = 'EQMT' --k_equipment
AND csm2.sty_id = cprdm2.sty_id
AND cprdm2.src_del_fl = 'N' --k_src_no_del
AND cprdm2.xxx_stat = 'A' --k_active_stat
AND csm2.src_del_fl = 'N' --k_src_no_del
AND csm2.xxx_stat = 'A' --k_active_stat
AND cosxm2.org_id = csm2.init_org_id
AND cosxm2.org_id = cprdm2.init_org_id
AND cprdm.colr_dsp_nbr = cprdm2.colr_dsp_nbr
AND csm.sty_dsp_nbr = csm2.sty_dsp_nbr
);
Thank you
Victor
-
Just a try ...
Change the qualifier of the column in the subquery to csm2:
Code:
AND NOT EXISTS
( SELECT csm2.sty_dsp_nbr
This might lead to better plan.
Hard to give another advice without knowledge of the logic behind the data etc.
Ales The whole difference between a little boy and an adult man is the price of toys
-
So you really are talking about a correlated vs. non correlated query. If it was me I would first separate out the tables into subqueries using inner and outer joins.
http://asktom.oracle.com/pls/asktom/...D:953229842074
I took a swag (scientific wild arsed guess) at rewriting the query with out not in or not exists. If may or may not work faster, but at leat you can figure out how the tables are joined. My guess spagetti. Don't be surprised if I made typos or other mistakes.
Code:
SELECT DISTINCT cprdm.prod_id, cprdm.sty_id,
cprdm.colr_comb_id, cprdm.colr_dsp_nbr,
cprcmtm.mkt_typ_id, cprsm.prod_reg_st_cd,
csm.sty_dsp_nbr, csm.src_sty_id,
csm.uom_cd, cum.uom_conv_fctr,
cprm.lnch_cd, -- Relay 164680 & 167034
cprm.lnch_dt, -- Relay 164680 & 167034
cmsm.mkstr_cd -- changes made on 25-Jun-08 DLM
FROM ( SELECT prod_id, reg_id, sor_fl, prod_reg_st_bdt,
prod_reg_st_xdt, prod_reg_st_cd
FROM cdb_prod_reg_st
WHERE cprsm.reg_id = 8 -- p_reg_id
AND cprsm.sor_fl = 'P' -- k_pwb -- diff. vs. USA/CANA/EMEA
AND TRUNC(cprsm.prod_reg_st_bdt) <= TRUNC(SYSDATE)
AND NVL( TRUNC(cprsm.prod_reg_st_xdt), TRUNC(SYSDATE)+1 ) > TRUNC(SYSDATE)
) cprsm
INNER JOIN cdb_sty csm
ON cprdm.sty_id = csm.sty_id
INNER JOIN cdb_sty_fcst csfm -- changes made on 25-Jun-08 DLM
ON cprsm.reg_id = cprm.reg_id -- changes made on 25-Jun-08 DLM
AND cprsm.prod_id = cprm.prod_id -- changes made on 25-Jun-08 DLM
AND csfm.sty_id = csm.sty_id -- changes made on 25-Jun-08 DLM
INNER JOIN cdb_prod cprdm
ON cprsm.prod_id = cprdm.prod_id
INNER JOIN cdb_prod_reg_cyc_mkt_typ cprcmtm
ON cprcmtm.prod_id = cprdm.prod_id
AND cprcmtm.mkt_typ_id = csfm.mkt_typ_id -- changes made on 25-Jun-08 DLM
AND cprcmtm.reg_id = 8 -- p_reg_id
INNER JOIN cdb_org_typ cotm
ON com.org_typ_cd = cotm.org_typ_cd
AND cotm.org_typ_desc = 'PRODUCT ENGINE' --k_product_engine
INNER JOIN cdb_uom cum
ON csm.uom_cd = cum.uom_cd
INNER JOIN cdb_mkt_str cmsm -- changes made on 25-Jun-08 DLM
ON csfm.mkstr_id = cmsm.mkstr_id -- changes made on 25-Jun-08 DLM
INNER JOIN cdb_org com
ON cprdm.init_org_id = com.org_id
INNER JOIN cdb_org_src_xref cosxm
ON com.org_id = cosxm.org_id
AND cosxm.lob_cd = 'APRL' --k_apparel
FULL OUTER JOIN
( SELECT csm.sty_dsp_nbr
FROM cdb_sty csm2
INNER JOIN cdb_prod cprdm2
ON csm2.sty_id = cprdm2.sty_id
AND csm2.init_org_id = cosxm2.org_id
INNER JOIN cdb_org_src_xref cosxm2
ON cprdm2.init_org_id = cosxm2.org_id
WHERE cosxm2.lob_cd = 'EQMT' --k_equipment
AND csm2.src_del_fl = 'N' --k_src_no_del
AND csm2.xxx_stat = 'A' --k_active_stat
AND cprdm2.src_del_fl = 'N' --k_src_no_del
AND cprdm2.xxx_stat = 'A') excl --k_active_stat
ON csm.sty_dsp_nbr = excl.sty_dsp_nbr
AND cprdm.colr_dsp_nbr = excl.colr_dsp_nbr
AND excl.sty_dsp_nbr IS NULL
AND excl.colr_dsp_nbr IS NULL;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|