Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: Tuning Join Condition in Query

    Hi all,

    I have the following issue to tune the below join condition in my query below:

    SELECT PO.COMPANY,
    PO.UPDATE_DATE,
    PO.EXTENDED_AMT,
    PO.LOCATION,
    ICLOCATION.R_NAME AS LOCATION_DESC,
    PO.VENDOR,
    AP.VENDOR_VNAME,
    MAJOR.DESCRIPTION AS MAJOR_DESC,
    MINOR.DESCRIPTION AS MINOR_DESC,
    CASE po.purch_majcl
    WHEN ' '
    THEN 'NONE'
    ELSE po.purch_majcl
    END AS PURCH_MAJ,
    CASE po.purch_min
    WHEN ' '
    THEN 'NONE'
    ELSE po.purch_min
    END AS PURCH_MIN,
    CASE po.manuf_code
    WHEN ' '
    THEN 'NO MFG'
    ELSE po.manuf_code
    END AS MANUF_CODE,
    CASE po.manuf_nbr
    WHEN ' '
    THEN 'NONE'
    ELSE po.manuf_nbr
    END AS MANUF_NBR,
    CASE po.manuf_division
    WHEN ' '
    THEN 'NONE'
    ELSE po.manuf_division
    END AS MANUF_DIV,
    CASE po.agreement_ref
    WHEN ' '
    THEN 'NONE'
    ELSE PO.AGREEMENT_REF
    END AS AGRMT_REF,

    /* ITEMLOC IS REQUIRED TO GET TO ICCACATEGORY */

    CASE po.item_type
    WHEN 'I'
    THEN ICCATEGORY.INV_ACCT_UNIT
    ELSE MMDIST.ACCT_UNIT
    END AS ACCT_UNIT,

    CASE po.item_type
    WHEN 'I'
    THEN ICCATEGORY.GL_CATEGORY
    ELSE to_char(MMDIST.ACCOUNT)
    END AS ACCOUNT,

    /* ITEMLOC IS REQUIRED TO GET TO ICCACATEGORY */

    PO.ITEM_TYPE,
    PO.ITEM,
    PO.DESCRIPTION AS ITEM_DESC,
    PO.ENT_REC_UOM,
    PO.ENT_RUOM_MULT,
    PO.ENT_REC_QTY,
    PO.ORIG_UNIT_CST,
    PO.MATCH_UNIT_CST,
    PO.PO_NUMBER,
    PO.PO_LINE_NBR,
    PO.QTY_TO_PROC,
    PURCHORDER.PO_DATE,
    PURCHORDER.BUYER_CODE,
    BUYER.R_NAME AS BUYER_NAME
    FROM LAW.PURCHORDER, LAWS81.BUYER,
    LAW.ICLOCATION, LAW.APVENMAST,
    LAW.PO LEFT JOIN LAW.MMDIST
    ON (PO.COMPANY = MMDIST.COMPANY
    AND PO.LOCATION = MMDIST.LOCATION
    AND PO.PO_NUMBER = MMDIST.DOC_NUMBER
    AND PO.PO_RELEASE = MMDIST.DOC_NBR_NUM
    AND PO.PO_CODE = MMDIST.PO_CODE
    AND PO.PO_LINE_NBR = MMDIST.LINE_NBR
    AND MMDIST.SYSTEM_CD = 'PO'
    AND MMDIST.DOC_TYPE = 'PT'
    AND MMDIST.COMPONENT_SEQ = 0
    AND MMDIST.AOC_CODE = ' ')

    /* 1st JOIN TO ITEMLOC */

    LEFT JOIN LAW.ITEMLOC
    ON PO.COMPANY = ITEMLOC.COMPANY
    AND PO.LOCATION = ITEMLOC.LOCATION
    AND PO.ITEM = ITEMLOC.ITEM,

    /* 2nd JOIN from ITEMLOC to ICCATEGORY */

    /* It should use the ITEMLOC join from above */
    /* creating cartesian product with itemloc */

    LAW.ITEMLOC LEFT JOIN LAW.ICCATEGORY
    ON ITEMLOC.COMPANY = ICCATEGORY.COMPANY
    AND ITEMLOC.LOCATION = ICCATEGORY.LOCATION
    AND ITEMLOC.GL_CATEGORY = ICCATEGORY.GL_CATEGORY,
    /* need to get to ICCATEGORY table using 2nd JOIN condition above */

    LAW.PO LEFT JOIN LAW.MAJOR
    ON PO.PURCH_MAJ = MAJOR.MAJOR_CLASS
    AND MAJOR.CLASS_TYPE = 'P'
    LEFT JOIN LAW.MINOR
    ON PO.PURCH_MAJCL = MINOR.MAJOR_CLASS
    AND PO.PURCH_MINCL = MINOR.MINOR_CLASS
    AND MINOR.CLASS_TYPE = 'P'
    WHERE PO.COMPANY = PO.COMPANY
    AND PO.PO_NUMBER = PO.PO_NUMBER
    AND PO.PO_RELEASE = PO.PO_RELEASE
    AND PO.PO_CODE = PO.PO_CODE
    AND PO.PO_LINE_NBR = PO.LINE_NBR
    AND PO.COMPANY = ICLOCATION.COMPANY
    AND PO.LOCATION = ICLOCATION.LOCATION
    AND PO.VENDOR = APVENMAST.VENDOR
    AND PO.COMPANY = PURCHORDER.COMPANY
    AND PO.PO_CODE = PURCHORDER.PO_CODE
    AND PO.PO_NUMBER = PURCHORDER.PO_NUMBER
    AND PO.PO_RELEASE = PURCHORDER.PO_RELEASE
    AND PURCHORDER.BUYER_CODE = BUYER.BUYER_CODE
    AND PO.R_STATUS = 1
    AND PO.ENT_REC_QTY > 0

    This query runs very slow and we cannot change the code to add or modify indexes. We need to join the 2nd table to the third table with optimized performance. How can I tune this slow running query with the best join conditions?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >we cannot change the code to add or modify indexes
    Looks like your only option is to add the undocument parameter to initSID.ora
    _make_slow_query_faster=TRUE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2003
    Posts
    369

    Can change join condition need tips

    Sorry for the confusion, yes we can change the query and need to improve join performance.

    Database is Oracle 10g on UNIX platform and we cannot add new index to environment. Is there way to re-write the query in this materialized view to optimize the joins?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •