I have a small query in which I need to do some simple calculations; however, it is taking forever to finish. I have used similar queries before on larger data and performance wise was way better. I tried doing sorting on source tables but the same happens my ETL moves 2 Rows per second... below is my query (any advise regarding tuning it is more than welcome)
SELECT t1.APPID, t1.APP_INS_ID, t1.APP_INS_ID_DUE_DTAE, TRUNC(LAST_DAY(ADD_MONTHS(t1.APP_INS_ID_DUE_DTAE, -1))) +1 AS EndDateOfPaymentMOnth, t1.APP_INS_ID_PAYMENT_DATE, t1.ispaid, (SELECT COUNT(APP_INS_ID) FROM tbl1 t2 WHERE t1.APPID = t2.APPID AND t2.APP_INS_ID <= t1.APP_INS_ID AND T2.Is_due = 1 AND ispaid = 'Y' AND TRUNC(APP_INS_ID_PAYMENT_DATE) <= TRUNC(LAST_DAY(ADD_MONTHS(t1.APP_INS_ID_DUE_DTAE, -1))) + 1) AS TOTAL_PAID FROM tbl1 t1 WHERE t1.is_due = 1 AND t1.APP_INS_ID_DUE_DTAE < TRUNC((SYSDATE - (EXTRACT(DAY FROM SYSDATE)) + 1))
Basically I am trying to calculate how many installments are paid up to installment due date.
Your help is really appreciated!
Here is a simple data with expected output:
APPID APP_INS_ID APP_INS_ID_DUE_DTAE EndDateOfPaymentMOnth APP_INS_ID_PAYMENT_DATE ispaid TOTAL_PAID 1| 1| 1-Aug-14| 1-Aug-14| 2-Oct-14| Y| 0| 1| 2| 1-Sep-14| 1-Sep-14| 2-Oct-14| Y| 0| 1| 3| 1-Oct-14| 1-Oct-14| 2-Oct-14| Y| 0| 1| 4| 1-Nov-14| 1-Nov-14| 2-Oct-14| Y| 4| 1| 5| 1-Dec-14| 1-Dec-14| 24-Nov-14| Y| 5| 1| 6| 1-Jan-15| 1-Jan-15| 9-Dec-14| Y| 6|