HOW TO AVOID NOT EXISTS IN THIS QUERY TO INCREASE PERFORMANCE

From: <venkat.oar_at_gmail.com>
Date: 11 Aug 2006 08:11:06 -0700
Message-ID: <1155309066.285926.81660@i42g2000cwa.googlegroups.com>

Hi,

the query below really kills, i am sure there is a way to finetune this query but not sure how to do it. Appreciate any help.

SELECT DISTINCT A.prod_sn

, TO_CHAR(a.est_bld_dt, 'YYYY-MM-DD')AS "Build_Date"
, SUBSTR(a.LN_SEQ_NO,8,3) AS "seq_no"
, c.sales_model_id
, c.fac_prod_fam_cd

FROM v_mes_supp_ord A

     , mes_rte_trak b
     , QWB2_SN_PFX c
WHERE A.prod_sn IS NOT NULL
      AND a.LN_SEQ_NO IS NOT NULL
      AND TRUNC(a.est_bld_dt) <= TRUNC(SYSDATE + 10)
      AND a.sn_pfx = c.sn_pfx
      AND c.fac_prod_fam_cd != 'MISC'
      AND NOT EXISTS (SELECT B2.Mov_No FROM  MES_SUPP_ORD_ASSEM
A2,MES_RTE_TRAK B2
			   WHERE  A2.supp_ord_tag = A.supp_ord_egg_tag AND
A2.supp_ord_assem_tag = B2.supp_ord_assem_tag
         AND b2.mov_no = 1 AND b2.area_id IN ('16C011280','16C011910'))

ORDER BY "Build_Date", "seq_no"

Thanks in advance
--Venkat Received on Fri Aug 11 2006 - 10:11:06 CDT