HOW TO IMPROVE PERFORMANCE ON SUM FUNCTION IN INLINE SQL QUERY
992212Feb 20 2013 — edited Feb 20 2013SELECT NVL(SUM(B1.T_AMOUNT),0) PAYMENT,B1.ACCOUNT_NUM,B1.BILL_SEQ
FROM
(
SELECT P.T_AMOUNT,P.ACCOUNT_NUM,P.BILL_SEQ
FROM PAYMENT_DATA_VIEW P
WHERE TRUNC(P.ACC_PAYMENT_DATE) < '01-JAN-2013'
AND P.CUSTOMER_NAME ='XYZ'
AND P.CLASS_ID IN (-1,1,2,94)
) B1
GROUP BY B1.ACCOUNT_NUM,B1.BILL_SEQ
Above is the query.If we run inner query it takes few second to execute but while we are summing up the same amount and bill_Seq using inline view, it takes time to execute it.
Note: Count of rows selected from inner query will be around >10 Lac
How to improve the performance for this query?
Pls suggest
Thanks in advance