DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: NOT EXISTS replace with NOT IN

  1. #1
    Join Date
    Dec 2008
    Posts
    3

    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

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    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

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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
  •  


Click Here to Expand Forum to Full Width