NOT EXISTS replace with NOT IN

[victory_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

[ales]

Change the qualifier of the column in the subquery to csm2:

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.

[gandolf989]

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.

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;