Hi All,
I am doing an performance tuning and I am facing the following issue
Old query,
---------------
SELECT AIA.INVOICE_NUM,
ACA.CHECK_DATE PAYMENT_DATE,
ACA.CHECK_NUMBER
FROM AP_INVOICES_ALL AIA,
(SELECT AIPAX.INVOICE_ID,
AIPAX.ORG_ID,
AIPAX.CHECK_ID,
SUM(AIPAX.AMOUNT) PAYMENT_AMT
FROM AP_INVOICE_PAYMENTS_ALL AIPAX
WHERE NVL(AIPAX.REVERSAL_FLAG,'N') ! = 'Y'
GROUP BY AIPAX.INVOICE_ID,
AIPAX.ORG_ID,
AIPAX.CHECK_ID
) AIPA,
AP_CHECKS_ALL ACA
WHERE
AIPA.INVOICE_ID(+) = AIA.INVOICE_ID
AND AIPA.ORG_ID(+) = AIA.ORG_ID
AND ACA.CHECK_ID(+) = AIPA.CHECK_ID
AND ACA.ORG_ID(+) = AIPA.ORG_ID
Modified Query
-----------------------
SELECT AIA.INVOICE_NUM,
ACA.CHECK_DATE PAYMENT_DATE,
ACA.CHECK_NUMBER
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA
WHERE
AIPA.INVOICE_ID(+) = AIA.INVOICE_ID
AND AIPA.ORG_ID(+) = AIA.ORG_ID
AND ACA.CHECK_ID(+) = AIPA.CHECK_ID
AND ACA.ORG_ID(+) = AIPA.ORG_ID
AND NVL(AIPA.REVERSAL_FLAG,'N')! = 'Y'
How the data is stored in table is,
AP_INVOICES_ALL has 1 row for each invoice
AP_INVOICE_PAYMENTS_ALL has 1 row for each payment (REVERSAL_FLAG is null) and 1 row if the payment is reversed (REVERSAL_FLAG is 'Y').
AP_CHECKS_ALL has 1 row for each payment
For example, For a invoice, a payment is made and it is reversed. So the data in table is
AP_INVOICES_ALL 1 row
AP_INVOICE_PAYMENTS_ALL 2 row with 1st as reversal flag null and 2nd as reversal flag 'Y'(It also has reference reversal payment id with 1st row as reference)
AP_CHECKS_ALL has 1 row
In old query, when I execute, the inline view wont return any data, but the inline view is outer joined and my overall query will return 1 row.
When I execute my modified query it doesnt return any data because I have put the reversal flag in the condition directly.
Iam developing this as a report(Oracle Report Builder).
Iam passing lexical parameters to AP_CHECKS_ALL. So I can not exclude AP_INVOICE_PAYMENTS_ALL and AP_CHECKS_ALL from my query.
I tried using union, its affecting the overall performance.
Any other workaround or alternative to handle this (Without affecting the performance)?
The actual query has lots of tables and conditions . To make the thread simple, I have explained only the issue part here.
Thanks in Advance.