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.

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. :confused: Don't be surprised if I made typos or other mistakes. :rolleyes:

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;