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

<venkat.oar_at_gmail.com> wrote in message news:1155309066.285926.81660_at_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
>

You don't seem to have a join condition
on table mes_rte_trak b. Is this a typo, or could it be relevant to your performance problem ?

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html