Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HOW TO AVOID NOT EXISTS IN THIS QUERY TO INCREASE PERFORMANCE

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

From: <nitelyjoy_at_ist-einmalig.de>
Date: 11 Aug 2006 13:29:40 -0700
Message-ID: <1155328180.191879.200310@75g2000cwc.googlegroups.com>

  1. Use EXPLAIN PLAN to tune SQL statements in general (-> oracle doc)
  2. Try a function based index on TRUNC(a.est_bld_dt) or perhaps substitute "TRUNC(a.est_bld_dt)" to "a.est_bld_dt" to get the index on that column working (create one, if not already exists).
  3. Maybe the "NOT EXISTS" is not the killer. Do you have indexes on all joining columns?

venkat.oar_at_gmail.com schrieb:

> 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 - 15:29:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US