5 Replies Latest reply: Jul 31, 2014 2:07 AM by davpet RSS

    How to fine tune the sql query

    eb02be0f-ff0f-4de6-89ff-b08d3fd8d340

      Hi To All,

       

       

      This query returns 4 records and its taking too much time of 5 min.

      The table  apps.ap_invoice_distributions_all is taking more time to fetch the data.

      Please help in fine tuning this query.

       

       

       

       

       

       

      SELECT

               inv_iv.item_description Expenditure_Type,

               accta.card_number Amex_Card_No,

               inv_iv.Employee Employee,

               accta.billed_date Statement_Date,

               NVL (inv_iv.expenditure_item_date, NVL (accta.transaction_date, inv_iv.creation_date) ) Transaction_Date,

               accta.billed_amount CC_Trx_Amount,

               inv_iv.amount Expend_Amount,

               inv_iv.invoice_num Invoice_No,

               DECODE (inv_iv.code_combination_id,

                       '-1', '-1',

                       gcc.segment1

                       || '.'

                       || gcc.segment2

                       || '.'

                       || gcc.segment3

                       || '.'

                       || gcc.segment4

                       ) gl_code,

               accta.merchant_name1|| '- '|| accta.merchant_city|| '- ' || accta.merchant_province_state Merchant,

               inv_iv.justification Justification,

               inv_iv.description Purpose_of_Report,

               inv_iv.invoice_amount Invoice_Total,

               TRUNC (SYSDATE) trunc_date_param,

               ' ' description_param,

               inv_iv.credit_card_trx_id credit_card_trx_id

         FROM  apps.ap_credit_card_trxns_all accta,

               apps.gl_code_combinations gcc,

              (SELECT  papf.full_name Employee,

                       aia.invoice_num,

                       aia.invoice_amount,

                       invlines_iv.amount,

                       invlines_iv.posted_flag,

                       invlines_iv.credit_card_trx_id,

                       invlines_iv.expenditure_item_date,

                       aia.org_id,

                       invlines_iv.justification,

                       aia.description,

                       aia.vendor_id,

                       aia.SOURCE,

                       invlines_iv.creation_date,

                       invlines_iv.dist_code_combination_id code_combination_id,

                       invlines_iv.description item_description

               FROM    apps.ap_invoices_all aia,

                       apps.po_vendors pv,

                       apps.per_all_people_f papf,                             

                      (SELECT *

                       FROM   apps.ap_invoice_distributions_all aida

                       WHERE  NVL (TRUNC (aida.expenditure_item_date),

                                   NVL (TRUNC (aida.creation_date),

                                        TO_DATE (TRUNC (SYSDATE), 'DD-MON-YYYY')

                                       )

                                  )

                              BETWEEN '01-JAN-2000'

                              AND     '02-JAN-2000' ) invlines_iv

               WHERE   aia.invoice_id = invlines_iv.invoice_id

               AND     aia.vendor_id = pv.vendor_id

               AND     pv.employee_id = papf.person_id(+)

               AND     papf.effective_start_date(+) <= TRUNC (SYSDATE)

               AND     papf.effective_end_date(+) >= TRUNC (SYSDATE)) inv_iv

         WHERE inv_iv.credit_card_trx_id = accta.trx_id(+)

         AND   inv_iv.code_combination_id = gcc.code_combination_id(+);