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