1 Replies - 688 Views - Last Post: 23 March 2016 - 06:45 AM

#1 oubinghose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 23-March 16

Optimize Oracle Query - Very Slow when extracting large data

Posted 23 March 2016 - 04:42 AM

Hi All,

I am new to sql reports development and I need the eye of an export to advise me how to optimize my Oracle SQL Query. Currently when I am running the query to extract large amount of data and the query takes more than 8-10 min to execute.

I do have a lot of outer joins and I believe this might be the issue. Grateful if anyone could check and advise me how to optimize my query

SELECT DISTINCT
b.operating_unit, 
b.trading_partner, 
b.invoice_date, 
b.type,
b.gl_date, 
b.invoice_num, 
b.invoice_id, 
b.quantity,
b.unit_price,
b.uom,
b.invoice_currency_code, 
b.payment_method_code, 
b.terms,
b.ITEM_DESCRIPTION,
b.LINE_NUMBER,
b.item_code,
b.VAT_CODE,
b.amount,
b.invoice_amount, 
b.vat_amount,
b.discount_amount,
b.price_variance,
b.total_amount,
b.status,
b.ship_to_location
FROM(
SELECT 
a.operating_unit, 
a.trading_partner, 
a.invoice_date, 
a.type,
a.gl_date, 
a.invoice_num, 
a.invoice_id, 
a.quantity,
ROUND(a.unit_price,2) unit_price,
a.uom,
a.invoice_currency_code, 
a.payment_method_code, 
a.terms,
a.LINE_NUMBER,
a.ITEM_DESCRIPTION,
a.item_code,
a.VAT_CODE,
--CASE WHEN a.status = 'CANCELLED' THEN NULL
--ELSE a.VAT_CODE END AS VAT_CODE,
sum(a.AMOUNT) amount,
a.invoice_amount, 
sum(a.vat_amount) vat_amount,
sum(a.discount_amount) discount_amount,
sum(a.price_variance) price_variance,
sum(a.total_amount) total_amount,
CASE WHEN a.status = 'FULLY' THEN 'Fully Applied'
WHEN a.status = 'UNAPPROVED' THEN 'Unvalidated'
WHEN a.status = 'NEEDS REAPPROVAL' THEN 'Needs Revalidation'
WHEN a.status = 'APPROVED' THEN 'Validated'
WHEN a.status = 'NEVER APPROVED' THEN 'Never Validated'
WHEN a.status = 'CANCELLED' THEN 'Cancelled'
WHEN a.status = 'UNPAID' THEN 'Unpaid'
WHEN a.status = 'AVAILABLE' THEN 'Available'
END AS status,
a.ship_to_location
from(
Select DISTINCT
    hz.name operating_unit,
    aia.INVOICE_TYPE_LOOKUP_CODE type,     
    aps.vendor_name trading_partner,    
    aia.INVOICE_DATE,    
    aia.gl_date, 
    aia.invoice_num, 
    aia.INVOICE_AMOUNT,          
    aida.invoice_id,
    APIDA.LINE_NUMBER,
    APIDA.QUANTITY_INVOICED QUANTITY,
    APIDA.UNIT_PRICE,
    APIDA.UOM,
    aia.INVOICE_CURRENCY_CODE,
    aia.PAYMENT_METHOD_CODE,    
    apt.name terms,
    APIDA.ITEM_DESCRIPTION,
    APIDA.ITEM_CODE,
    case when apida.line_type_lookup_code <> 'IPV' THEN APIDA.AMOUNT ELSE 0 END AS AMOUNT,  
    --case when aida.line_type_lookup_code = 'REC_TAX' THEN aida.RECOVERY_RATE_NAME END AS VAT_CODE, 
    APIDA.VAT_CODE,
    APIDA.VAT_AMOUNT,
    0 DISCOUNT_AMOUNT,
    case when apida.line_type_lookup_code = 'IPV' THEN APIDA.AMOUNT ELSE 0 END AS PRICE_VARIANCE,      
    APIDA.TOTAL_AMOUNT, 
    APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
            (
             aia.INVOICE_ID
            ,aia.INVOICE_AMOUNT
            ,aia.PAYMENT_STATUS_FLAG
            ,aia.INVOICE_TYPE_LOOKUP_CODE
            ) status,
    APIDA.SHIP_TO_LOCATION  
    --b.description ship_to_location
from (SELECT AILA.INVOICE_ID, AILA.LINE_TYPE_LOOKUP_CODE, AILA.LINE_NUMBER, aila.TAX_CLASSIFICATION_CODE VAT_CODE, AILA.QUANTITY_INVOICED, AILA.UNIT_PRICE, AILA.UNIT_MEAS_LOOKUP_CODE UOM, X.invoice_distribution_id, X.Description ITEM_DESCRIPTION, msi.segment1 item_code, NVL(X.AMOUNT, 0) AMOUNT, NVL(B.TAX_AMOUNT,0) VAT_AMOUNT, (NVL(X.AMOUNT, 0) +  NVL(B.TAX_AMOUNT,0)) TOTAL_AMOUNT,HR.DESCRIPTION SHIP_TO_LOCATION 
FROM ap_invoice_lines_all aila, ap_invoice_distributions_all X, MTL_SYSTEM_ITEMS msi, hr_locations hr,
 (SELECT A.INVOICE_ID, A.LINE_TYPE_LOOKUP_CODE, A.INVOICE_LINE_NUMBER,  A.CHARGE_APPLICABLE_TO_DIST_ID, SUM(A.AMOUNT) TAX_AMOUNT
        FROM ap_invoice_distributions_all A
        WHERE 1=1
        AND A.LINE_TYPE_LOOKUP_CODE  = 'REC_TAX'
        GROUP BY A.INVOICE_ID, A.LINE_TYPE_LOOKUP_CODE, A.INVOICE_LINE_NUMBER,  A.CHARGE_APPLICABLE_TO_DIST_ID) B 
WHERE AILA.INVOICE_ID = X.INVOICE_ID(+)
AND X.INVOICE_ID =  B.INVOICE_ID(+)
AND X.invoice_distribution_id = B.CHARGE_APPLICABLE_TO_DIST_ID(+)
and msi.inventory_item_id(+) = aila.inventory_item_id 
AND AILA.SHIP_TO_LOCATION_ID = HR.SHIP_TO_LOCATION_ID
and aila.line_number = X.INVOICE_LINE_NUMBER(+)
AND AILA.LINE_TYPE_LOOKUP_CODE  != 'REC_TAX' AND AILA.LINE_TYPE_LOOKUP_CODE  != 'NONREC_TAX' 
--AND AILA.INVOICE_ID = '10T52233547'
)APIDA,
ap_invoice_distributions_all aida, ap_invoices_all aia, ap_suppliers aps, ap_terms apt, hr_organization_units hz
where aia.invoice_id = aida.invoice_id(+)
and aia.invoice_id = APIDA.invoice_id
and aps.vendor_id = aia.vendor_id
and apt.term_id = aia.terms_id
and hz.ORGANIZATION_ID = aia.org_id
--and aia.invoice_NUM = '123456'
--and aida.LINE_TYPE_LOOKUP_CODE = 'REC_TAX'
/* Parameters */
and hz.ORGANIZATION_ID between NVL(:P/>_operating_unit_from, hz.ORGANIZATION_ID) and NVL(:P/>_operating_unit_to, hz.ORGANIZATION_ID)
and aia.INVOICE_DATE between NVL(:P/>_invoice_date_from, aia.INVOICE_DATE) and NVL(:P/>_invoice_date_to, aia.INVOICE_DATE)
and aps.vendor_id between NVL(:P/>_trading_partner_from, aps.vendor_id) and NVL(:P/>_trading_partner_to, aps.vendor_id)
and aia.gl_date between NVL(:P/>_gl_date_from, aia.gl_date) and NVL(:P/>_gl_date_to, aia.gl_date)
order by hz.name, aps.vendor_name, APIDA.SHIP_TO_LOCATION, APIDA.ITEM_DESCRIPTION)a
group by a.operating_unit, 
a.trading_partner, 
a.invoice_date, 
a.gl_date, 
a.invoice_num, 
a.invoice_amount, 
a.invoice_id, 
a.invoice_currency_code, 
a.payment_method_code, 
a.terms,
a.ITEM_DESCRIPTION,
a.item_code, 
a.VAT_CODE, 
a.quantity,
a.unit_price,
a.LINE_NUMBER,
a.uom,
a.status,
a.type,
a.ship_to_location)b
order by b.operating_unit, b.trading_partner, b.ship_to_location, b.invoice_date, b.gl_date, b.invoice_num, b.LINE_NUMBER



Is This A Good Question/Topic? 0
  • +

Replies To: Optimize Oracle Query - Very Slow when extracting large data

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13389
  • View blog
  • Posts: 53,428
  • Joined: 12-June 08

Re: Optimize Oracle Query - Very Slow when extracting large data

Posted 23 March 2016 - 06:45 AM

I would suggest looking at the execution plan first.

https://docs.oracle....533/ex_plan.htm
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1