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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Aug 2006 10:43:22 -0700
Message-ID: <1155318202.566371.305580@i42g2000cwa.googlegroups.com>

venkat.oar_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

If you were to look at a 10046 trace for the SQL statement, you would likely find a large number of full table scans and likely a very ugly Cartesian join (problem cause identified by Jonathan Lewis). You are not giving Oracle much to work with here. The TRUNC(a.est_bld_dt) means that unless there is a function based index on TRUNC(est_bld_dt), an index on the a.est_bld_dt column will be of no use. The Cartesian join due to not constraining the mes_rte_trak table combined with the NOT EXISTS syntax...

Try something like this:
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,
  (SELECT
    A2.SUPP_ORD_TAG,
    B2.MOV_NO
  FROM
    MES_SUPP_ORD_ASSEM A2,
    MES_RTE_TRAK B2
  WHERE
    A2.SUPP_ORD_ASSEM_TAG = B2.SUPP_ORD_ASSEM_TAG     AND B2.MOV_NO = 1
    AND B2.AREA_ID IN ('16C011280','16C011910')) D WHERE
  A.PROD_SN IS NOT NULL

  AND A.LN_SEQ_NO IS NOT NULL
  AND A.EST_BLD_DT < TRUNC(SYSDATE + 11)-.00000001
  AND A.SN_PFX = C.SN_PFX
  AND C.FAC_PROD_FAM_CD != 'MISC'
  AND B.???? = ????
  AND A.SUPP_ORD_EGG_TAG=D.SUPP_ORD_TAG(+)
  AND D.SUPP_ORD_TAG IS NULL

ORDER BY
  "BUILD_DATE",
  "SEQ_NO"; Take a look at the indexes on the tables. Are there any indexes that Oracle can use with the requirements that you specified? An index on C.FAC_PROD_FAM_CD would likely be of limited or no use for this SQL statement, so not all indexes are good indexes for every SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Aug 11 2006 - 12:43:22 CDT