1 Reply Latest reply: Mar 5, 2010 7:50 AM by AnilSharma RSS

    Query optimization

    hina
      Hi

      I have following query related to purchasing and payables modules that show outstanding receipts.

      SELECT
      PHA.SEGMENT1 ERP_PO
      ,PHA.APPROVED_DATE PO_DATE
      ,PLA.QUANTITY PO_QTY
      ,S.VENDOR_ID
      ,S.VENDOR_NAME
      ,SA.VENDOR_SITE_CODE
      ,SH.RECEIPT_NUM
      ,RT.TRANSACTION_DATE
      ,I.CONCATENATED_SEGMENTS ITEM_CODE
      ,I.ATTRIBUTE2 LEGACY_CODE
      ,SL.ITEM_DESCRIPTION
      ,CV.SEGMENT1 STORE_NAME
      ,RT.UOM_CODE
      ,RT.QUANTITY + NVL(PCORR.QUANTITY,0)-NVL(SUM(ILA.QUANTITY_INVOICED),0) RCT_QUANTITY
      ,RT.TRANSACTION_TYPE
      ,O.ORGANIZATION_CODE
      ,rt.po_unit_price
      ,(RT.QUANTITY +NVL(PCORR.QUANTITY,0)-NVL(SUM(ILA.QUANTITY_INVOICED),0))*rt.po_unit_price Q_VALUE
      ,PHA.ORG_ID
      ,PLA.PO_LINE_ID
      ,SL.SHIPMENT_LINE_ID
      ,RT.TRANSACTION_ID
      FROM
      RCV_TRANSACTIONS RT
      ,RCV_TRANSACTIONS PCORR
      ,RCV_SHIPMENT_HEADERS SH
      ,PO_HEADERS_ALL PHA
      ,PO_LINES_ALL PLA
      ,PO_LINE_LOCATIONS_ALL PLLA
      ,RCV_SHIPMENT_LINES SL
      ,AP_SUPPLIERS S
      ,AP_SUPPLIER_SITES_ALL SA
      ,ORG_ORGANIZATION_DEFINITIONS O
      ,MTL_SYSTEM_ITEMS_VL I
      ,MTL_ITEM_CATEGORIES IC
      ,MTL_CATEGORIES_VL CV
      ,AP_INVOICE_LINES_ALL ILA
      WHERE
      RT.SHIPMENT_HEADER_ID=SH.SHIPMENT_HEADER_ID
      AND RT.ORGANIZATION_ID=SH.SHIP_TO_ORG_ID
      AND RT.PO_HEADER_ID=PHA.PO_HEADER_ID
      AND RT.TRANSACTION_TYPE='DELIVER'
      AND RT.SHIPMENT_HEADER_ID=SL.SHIPMENT_HEADER_ID
      AND RT.SHIPMENT_LINE_ID=SL.SHIPMENT_LINE_ID
      AND PHA.VENDOR_ID=S.VENDOR_ID
      AND PHA.VENDOR_ID=SA.VENDOR_ID
      AND PHA.VENDOR_SITE_ID=SA.VENDOR_SITE_ID
      AND PHA.ORG_ID=SA.ORG_ID
      AND RT.VENDOR_ID=SA.VENDOR_ID
      AND RT.VENDOR_SITE_ID=SA.VENDOR_SITE_ID

      AND RT.ORGANIZATION_ID=O.ORGANIZATION_ID
      AND SH.SHIP_TO_ORG_ID=O.ORGANIZATION_ID
      AND O.ORGANIZATION_ID<>82
      AND SL.ITEM_ID=I.INVENTORY_ITEM_ID
      AND I.ORGANIZATION_ID<>82
      AND SH.SHIP_TO_ORG_ID=I.ORGANIZATION_ID
      AND I.ORGANIZATION_ID=O.ORGANIZATION_ID
      AND RT.TRANSACTION_ID=PCORR.PARENT_TRANSACTION_ID(+)
      AND PCORR.TRANSACTION_TYPE (+) = ('CORRECT')
      AND RT.TRANSACTION_ID NOT IN (SELECT PARENT_TRANSACTION_ID FROM RCV_TRANSACTIONS CORR WHERE CORR.TRANSACTION_TYPE='CORRECT' AND RT.TRANSACTION_ID=CORR.PARENT_TRANSACTION_ID AND RT.QUANTITY=ABS(CORR.QUANTITY))

      AND PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
      AND PLA.ORG_ID=PHA.ORG_ID
      AND PLA.PO_HEADER_ID=RT.PO_HEADER_ID
      AND PLA.PO_LINE_ID=RT.PO_LINE_ID
      AND PLA.PO_HEADER_ID=SL.PO_HEADER_ID
      AND PLA.PO_LINE_ID=SL.PO_LINE_ID
      AND PLA.ITEM_ID=I.INVENTORY_ITEM_ID
      AND PLA.ORG_ID=O.OPERATING_UNIT
      AND NVL(PLA.CANCEL_FLAG,'N')<>'Y'
      AND PLA.CANCEL_DATE IS NULL

      AND PLLA.PO_HEADER_ID=PHA.PO_HEADER_ID
      AND PLLA.PO_LINE_ID=PLA.PO_LINE_ID
      AND PLLA.ORG_ID=PLA.ORG_ID
      AND PLLA.LINE_LOCATION_ID=RT.PO_LINE_LOCATION_ID
      AND PLLA.PO_HEADER_ID=RT.PO_HEADER_ID
      AND PLLA.PO_LINE_ID=RT.PO_LINE_ID

      AND I.INVENTORY_ITEM_ID=IC.INVENTORY_ITEM_ID
      AND I.ORGANIZATION_ID=IC.ORGANIZATION_ID
      AND IC.ORGANIZATION_ID<>82
      AND IC.CATEGORY_ID=CV.CATEGORY_ID
      AND CV.DISABLE_DATE IS NULL

      AND SL.PO_HEADER_ID=ILA.PO_HEADER_ID (+)
      AND SL.PO_LINE_ID=ILA.PO_LINE_ID (+)
      AND SL.PO_LINE_LOCATION_ID=ILA.PO_LINE_LOCATION_ID(+)
      AND SL.SHIPMENT_LINE_ID=ILA.RCV_SHIPMENT_LINE_ID(+)
      AND ILA.CANCELLED_FLAG(+) <>'Y'
      AND ILA.AMOUNT (+) <>0


      AND TO_DATE(RT.TRANSACTION_DATE,'DD-MON-YY') <='05-MAR-10'--BETWEEN DATE_FROM AND DATE_TO
      HAVING
      RT.QUANTITY + NVL(PCORR.QUANTITY,0)-NVL(SUM(ILA.QUANTITY_INVOICED),0)<>0
      GROUP BY
      PHA.SEGMENT1
      ,PHA.APPROVED_DATE
      ,PLA.QUANTITY
      ,S.VENDOR_NAME
      ,SA.VENDOR_SITE_CODE
      ,SH.RECEIPT_NUM
      ,RT.TRANSACTION_DATE
      ,I.CONCATENATED_SEGMENTS
      ,SL.ITEM_DESCRIPTION
      ,CV.SEGMENT1
      ,RT.UOM_CODE
      ,RT.QUANTITY + NVL(PCORR.QUANTITY,0)
      ,RT.TRANSACTION_TYPE
      ,O.ORGANIZATION_CODE
      ,rt.po_unit_price
      ,(RT.QUANTITY + NVL(PCORR.QUANTITY,0))*rt.po_unit_price
      ,PHA.ORG_ID
      ,PLA.PO_LINE_ID
      ,SL.SHIPMENT_LINE_ID
      ,RT.TRANSACTION_ID
      ,S.VENDOR_ID
      ,I.ATTRIBUTE2


      it gives output in 40 seconds maximum.

      But when i add criteria to show me the results of only one operating unit (query below) then although the number of rows returned are less but query gives output in 2 hours. any guideline?


      SELECT
      PHA.SEGMENT1 ERP_PO
      ,PHA.APPROVED_DATE PO_DATE
      ,PLA.QUANTITY PO_QTY
      ,S.VENDOR_ID
      ,S.VENDOR_NAME
      ,SA.VENDOR_SITE_CODE
      ,SH.RECEIPT_NUM
      ,RT.TRANSACTION_DATE
      ,I.CONCATENATED_SEGMENTS ITEM_CODE
      ,I.ATTRIBUTE2 LEGACY_CODE
      ,SL.ITEM_DESCRIPTION
      ,CV.SEGMENT1 STORE_NAME
      ,RT.UOM_CODE
      ,RT.QUANTITY + NVL(PCORR.QUANTITY,0)-NVL(SUM(ILA.QUANTITY_INVOICED),0) RCT_QUANTITY
      ,RT.TRANSACTION_TYPE
      ,O.ORGANIZATION_CODE
      ,rt.po_unit_price
      ,(RT.QUANTITY +NVL(PCORR.QUANTITY,0)-NVL(SUM(ILA.QUANTITY_INVOICED),0))*rt.po_unit_price Q_VALUE
      ,PHA.ORG_ID
      ,PLA.PO_LINE_ID
      ,SL.SHIPMENT_LINE_ID
      ,RT.TRANSACTION_ID
      FROM
      RCV_TRANSACTIONS RT
      ,RCV_TRANSACTIONS PCORR
      ,RCV_SHIPMENT_HEADERS SH
      ,PO_HEADERS_ALL PHA
      ,PO_LINES_ALL PLA
      ,PO_LINE_LOCATIONS_ALL PLLA
      ,RCV_SHIPMENT_LINES SL
      ,AP_SUPPLIERS S
      ,AP_SUPPLIER_SITES_ALL SA
      ,ORG_ORGANIZATION_DEFINITIONS O
      ,MTL_SYSTEM_ITEMS_VL I
      ,MTL_ITEM_CATEGORIES IC
      ,MTL_CATEGORIES_VL CV
      ,AP_INVOICE_LINES_ALL ILA
      WHERE
      RT.SHIPMENT_HEADER_ID=SH.SHIPMENT_HEADER_ID
      AND RT.ORGANIZATION_ID=SH.SHIP_TO_ORG_ID
      AND RT.PO_HEADER_ID=PHA.PO_HEADER_ID
      AND RT.TRANSACTION_TYPE='DELIVER'
      AND RT.SHIPMENT_HEADER_ID=SL.SHIPMENT_HEADER_ID
      AND RT.SHIPMENT_LINE_ID=SL.SHIPMENT_LINE_ID
      AND PHA.VENDOR_ID=S.VENDOR_ID

      AND PHA.VENDOR_ID=SA.VENDOR_ID
      AND PHA.VENDOR_SITE_ID=SA.VENDOR_SITE_ID
      AND PHA.ORG_ID=SA.ORG_ID
      AND RT.VENDOR_ID=SA.VENDOR_ID
      AND RT.VENDOR_SITE_ID=SA.VENDOR_SITE_ID

      AND RT.ORGANIZATION_ID=O.ORGANIZATION_ID
      AND SH.SHIP_TO_ORG_ID=O.ORGANIZATION_ID
      AND O.ORGANIZATION_ID<>82
      AND SL.ITEM_ID=I.INVENTORY_ITEM_ID
      AND I.ORGANIZATION_ID<>82
      AND SH.SHIP_TO_ORG_ID=I.ORGANIZATION_ID
      AND I.ORGANIZATION_ID=O.ORGANIZATION_ID
      AND RT.TRANSACTION_ID=PCORR.PARENT_TRANSACTION_ID(+)
      AND PCORR.TRANSACTION_TYPE (+) = ('CORRECT')
      AND RT.TRANSACTION_ID NOT IN (SELECT PARENT_TRANSACTION_ID FROM RCV_TRANSACTIONS CORR WHERE CORR.TRANSACTION_TYPE='CORRECT' AND RT.TRANSACTION_ID=CORR.PARENT_TRANSACTION_ID AND RT.QUANTITY=ABS(CORR.QUANTITY))

      AND PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
      AND PLA.ORG_ID=PHA.ORG_ID
      AND PLA.PO_HEADER_ID=RT.PO_HEADER_ID
      AND PLA.PO_LINE_ID=RT.PO_LINE_ID
      AND PLA.PO_HEADER_ID=SL.PO_HEADER_ID
      AND PLA.PO_LINE_ID=SL.PO_LINE_ID
      AND PLA.ITEM_ID=I.INVENTORY_ITEM_ID
      AND PLA.ORG_ID=O.OPERATING_UNIT
      AND NVL(PLA.CANCEL_FLAG,'N')<>'Y'
      AND PLA.CANCEL_DATE IS NULL

      AND PLLA.PO_HEADER_ID=PHA.PO_HEADER_ID
      AND PLLA.PO_LINE_ID=PLA.PO_LINE_ID
      AND PLLA.ORG_ID=PLA.ORG_ID
      AND PLLA.LINE_LOCATION_ID=RT.PO_LINE_LOCATION_ID
      AND PLLA.PO_HEADER_ID=RT.PO_HEADER_ID
      AND PLLA.PO_LINE_ID=RT.PO_LINE_ID

      AND I.INVENTORY_ITEM_ID=IC.INVENTORY_ITEM_ID
      AND I.ORGANIZATION_ID=IC.ORGANIZATION_ID
      AND IC.ORGANIZATION_ID<>82
      AND IC.CATEGORY_ID=CV.CATEGORY_ID
      AND CV.DISABLE_DATE IS NULL

      AND SL.PO_HEADER_ID=ILA.PO_HEADER_ID (+)
      AND SL.PO_LINE_ID=ILA.PO_LINE_ID (+)
      AND SL.PO_LINE_LOCATION_ID=ILA.PO_LINE_LOCATION_ID(+)
      AND SL.SHIPMENT_LINE_ID=ILA.RCV_SHIPMENT_LINE_ID(+)
      AND ILA.CANCELLED_FLAG(+) <>'Y'
      AND ILA.AMOUNT (+) <>0


      and o.OPERATING_UNIT=90


      AND TO_DATE(RT.TRANSACTION_DATE,'DD-MON-YY') <='05-MAR-10'--BETWEEN DATE_FROM AND DATE_TO
      HAVING
      RT.QUANTITY + NVL(PCORR.QUANTITY,0)-NVL(SUM(ILA.QUANTITY_INVOICED),0)<>0
      GROUP BY
      PHA.SEGMENT1
      ,PHA.APPROVED_DATE
      ,PLA.QUANTITY
      ,S.VENDOR_NAME
      ,SA.VENDOR_SITE_CODE
      ,SH.RECEIPT_NUM
      ,RT.TRANSACTION_DATE
      ,I.CONCATENATED_SEGMENTS
      ,SL.ITEM_DESCRIPTION
      ,CV.SEGMENT1
      ,RT.UOM_CODE
      ,RT.QUANTITY + NVL(PCORR.QUANTITY,0)
      ,RT.TRANSACTION_TYPE
      ,O.ORGANIZATION_CODE
      ,rt.po_unit_price
      ,(RT.QUANTITY + NVL(PCORR.QUANTITY,0))*rt.po_unit_price
      ,PHA.ORG_ID
      ,PLA.PO_LINE_ID
      ,SL.SHIPMENT_LINE_ID
      ,RT.TRANSACTION_ID
      ,S.VENDOR_ID
      ,I.ATTRIBUTE2

      Edited by: user11969354 on Mar 5, 2010 3:00 AM
        • 1. Re: Query optimization
          AnilSharma
          Also post the thread in SQL/PLSQL to get quick reply

          PL/SQL

          Thanks
          --Anil