Re: HOW TO AVOID NOT EXISTS IN THIS QUERY TO INCREASE PERFORMANCE

venkat...._at_gmail.com wrote:
> 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

Anyone who says that they can help you from the information you've provided is a liar :-)

Please provide the group:

1. Full Oracle version information (incredibly important)
2. DDL on the tables involved
3. Sample data
4. An EXPLAIN PLAN showing how the query currently behaves

HTH -g Received on Fri Aug 11 2006 - 10:16:11 CDT