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;