Dear All,
I have written a procedure inside which I have declared 2 cursors. Cursor 1 is calling Cursor 2 on the flow.
Cursor 1 - It fetches a list of applications along with all details from the database as per the given date. Below is the cursor query.
VARC1 C1%ROWTYPE;
Cursor 2 - I have another table which contains verification messages related to each application. One application will have multiple verification messages. I just want to fetch the latest message from the table by passing the application id.
While fetching the data for 1 month period its takes approximately about 30 minutes about 500-1000 records. Without the cursor 2 the same data fetching takes only 1 minute to fetch. Can anyone please help me with optimizing the queries ?
Cursor queries below :
Cursor 1 -
CURSOR C1 IS
Select <set of coulmns> from (select <set of columns>,row_number() over ( partition by T1.id order by E.id) rn FROM
HORG_ODS.HORIGINATIONS$APPLICANT E,
HORG_ODS.HORIGINATIO$APPLICAN_APPLICATI A,
HORG_ODS.HORIGINATIONS$APPLICATION T1,
HORG_ODS.HORIGINATIONS$APPLICATION_DATA T2,
HORG_ODS.HORIGINATIONS$DATA_REQUEST T3,
HORG_ODS.HORIGINA$REQUE_APPLICATIONREQU T4,
HORG_ODS.DECISION$APPLICATIONREQUESTDEC T41,
HORG_ODS.DECISIO$APPLICATIONROU_DECISIO T5,
HORG_ODS.DECISI$BUREAUSCORIN_APPLICATIO T6,
HORG_ODS.DECISIONS$BUREAUSCORINGDECISIO T7,
HORG_ODS.HORIGINAT$APPLICAT_REQUESTEDPR T8,
HORG_ODS.HORIGINATIONS$REQUESTEDPRODUCT T9,
HORG_ODS.HORIGINAT$REQUESTEDPR_CREDITCA T10,
HORG_ODS.HORIGINATIONS$CREDITCARD T11,
HORG_ODS.HORIGINAT$REQUESTEDPR_VEHICLEL T12,
HORG_ODS.HORIGINATIONS$VEHICLELOAN T13,
HORG_ODS.HORIGINATIONS$ADDRESS T14,
HORG_ODS.HORIGINATIO$ADDRESSE_APPLICANT T15,
HORG_ODS.HORIGINATIONS$ADDRESS T42,
HORG_ODS.HORIGINATIO$ADDRESSE_APPLICANT T43,
(select T40.*,
T17.HORIGINATIONS$APPLICANTID APPLICANTID FROM
HORG_ODS.HORIGINATIONS$APPLICATION T39,
HORG_ODS.HORIGINATIO$APPLICAN_APPLICATI A,
HORG_ODS.HORIGINATIONS$BANKDETAILS T40,
HORG_ODS.HORIGINATIO$BANKDETAI_APPLICAN T17
WHERE T39.id = A.HORIGINATIONS$APPLICATIONID
AND T17.HORIGINATIONS$APPLICANTID = A.HORIGINATIONS$APPLICANTID
AND T39.CLIENTCODE = T40.BANKCODE
AND T17.HORIGINATIONS$BANKDETAILSID = T40.ID
) T16,
HORG_ODS.HORIGINATIONS$COLLATERAL T18,
HORG_ODS.HORIGINATIO$COLLATERA_APPLICAN T19,
HORG_ODS.HORIGINATIONS$EMPLOYMENTDETAIL T20,
HORG_ODS.HORIGINAT$EMPLOYMENTDE_APPLICA T21,
HORG_ODS.HORIGINATIONS$IDDOCUMENT T22,
HORG_ODS.HORIGINATIO$IDDOCUMEN_APPLICAN T23,
HORG_ODS.HORIGINATIONS$STATEDASSET T24,
HORG_ODS.HORIGINATIO$STATEDASS_APPLICAN T25,
HORG_ODS.HORIGINATIONS$STATEDINCOME T26,
HORG_ODS.HORIGINATI$STATEDINCO_APPLICAN T27,
HORG_ODS.HORIGINATIONS$STATEDLIABILITY T28,
HORG_ODS.HORIGINAT$STATEDLIABIL_APPLICA T29,
HORG_ODS.HORIGINATIONS$TELEPHONE T30,
HORG_ODS.HORIGINATIO$TELEPHONE_APPLICAN T31,
HORG_ODS.DECISI$CUSTOMSCORIN_APPLICATIO T32,
HORG_ODS.DECISIONS$CUSTOMSCORINGDECISIO T33,
HORG_ODS.DECI$PREBUREAUAPPLICA_APPLICAT T34,
HORG_ODS.DECISI$PREBUREAUAPPLICATIONROU T35,
HORG_ODS.DECI$POSTBUREAUAPPLIC_APPLICAT T36,
HORG_ODS.DECISI$POSTBUREAUAPPLICATIONRO T37,
(select E.ID, sum(T26.netamount) TOTALNETINCOME
from HORG_ODS.HORIGINATIONS$APPLICANT E, HORG_ODS.HORIGINATIONS$STATEDINCOME T26,
HORG_ODS.HORIGINATI$STATEDINCO_APPLICAN T27
where
T26.ID(+) = T27.HORIGINATIONS$STATEDINCOMEID
AND E.id = T27.HORIGINATIONS$APPLICANTID(+)
group by E.ID) T38,
HORG_ODS.HORIGINATI$CONSUMERSU_APPLICAT T44,
HORG_ODS.HORIGINATIONS$CONSUMERSUMMARY T45,
HORG_ODS.HORIGINATION$APPLICAN_IDSOURCE T46,
HORG_ODS.HORIGINATIONS$IDSOURCE T47
WHERE T1.clientId = CLIENT_ID
AND T1.id = A.HORIGINATIONS$APPLICATIONID
AND E.id = A.HORIGINATIONS$APPLICANTID
AND E.ID = T38.ID
AND T14.ID(+) = T15.HORIGINATIONS$ADDRESSID
AND E.id = T15.HORIGINATIONS$APPLICANTID(+)
AND T14.ADDRESSTYPE = 'HOME'
AND T42.ID(+) = T43.HORIGINATIONS$ADDRESSID
AND E.id = T43.HORIGINATIONS$APPLICANTID(+)
AND T42.ADDRESSTYPE = 'WORK'
AND E.ID = T16.APPLICANTID
AND T18.ID(+) = T19.HORIGINATIONS$COLLATERALID
AND E.id = T19.HORIGINATIONS$APPLICANTID(+)
AND T20.ID(+) = T21.HORIGINATION$EMPLOYMENTDETAILS
AND E.id = T21.HORIGINATIONS$APPLICANTID(+)
AND T20.ISCURRENT = 1
AND T22.ID(+) = T23.HORIGINATIONS$IDDOCUMENTID
AND E.id = T23.HORIGINATIONS$APPLICANTID(+)
AND T24.ID(+) = T25.HORIGINATIONS$STATEDASSETID
AND E.id = T25.HORIGINATIONS$APPLICANTID(+)
AND T26.ID(+) = T27.HORIGINATIONS$STATEDINCOMEID
AND E.id = T27.HORIGINATIONS$APPLICANTID(+)
AND T28.ID(+) = T29.HORIGINATIONS$STATEDLIABILITYI
AND E.id = T29.HORIGINATIONS$APPLICANTID(+)
AND T30.ID(+) = T31.HORIGINATIONS$TELEPHONEID
AND E.id = T31.HORIGINATIONS$APPLICANTID(+)
AND T30.TELEPHONETYPE = 'HOME'
AND T1.id = T2.HORIGINATIONS$APPLICATIONID
AND T3.HORIGINATIONS$DATAID = T2.HORIGINATIONS$DATAID
AND T4.HORIGINATIONS$REQUESTID = T3.HORIGINATIONS$REQUESTID
AND T41.id = T4.DECISIO$APPLICATIONREQUESTDECI
AND T5.DECISIO$APPLICATIONREQUESTDECI = T4.DECISIO$APPLICATIONREQUESTDECI
AND T6.DECISIONS$APPLICATIONROUTINGID = T5.DECISIONS$APPLICATIONROUTINGID
AND T7.id(+) = T6.DECISION$BUREAUSCORINGDECISION
AND T34.DECISIONS$APPLICATIONROUTINGID = T5.DECISIONS$APPLICATIONROUTINGID
AND T35.id(+) = T34.DECISI$PREBUREAUAPPLICATIONROU
AND T36.DECISIONS$APPLICATIONROUTINGID = T5.DECISIONS$APPLICATIONROUTINGID
AND T37.id(+) = T36.DECISI$POSTBUREAUAPPLICATIONRO
AND T32.DECISIONS$APPLICATIONROUTINGID = T5.DECISIONS$APPLICATIONROUTINGID
AND T33.id = T32.DECISION$CUSTOMSCORINGDECISION
AND T8.HORIGINATIONS$APPLICATIONID = T1.id
AND T9.id = T8.HORIGINATIONS$REQUESTEDPRODUCT
AND T9.id = T10.HORIGINATIONS$REQUESTEDPRODUCT
AND T10.HORIGINATIONS$CREDITCARDID = T11.id
AND T9.id = T12.HORIGINATIONS$REQUESTEDPRODUCT
AND T12.HORIGINATIONS$VEHICLELOANID = T13.id
AND T45.ID(+) = T44.HORIGINATIONS$CONSUMERSUMMARYI
AND T1.id = T44.HORIGINATIONS$APPLICATIONID(+)
AND T47.ID(+) = T46.HORIGINATIONS$IDSOURCEID
AND E.ID = T46.HORIGINATIONS$APPLICANTID(+)
AND T1.CREATEDDATE between to_date(FROM_DATE, 'DD/MM/YYYY') AND to_date(T_DATE, 'DD/MM/YYYY')
--AND (T1.APPLICATIONSTATE IN ('APPROVED', 'REJECTED') OR (T1.PROCESSINGPHASE = 'RISK_SCORING'))
ORDER BY T1.id, apcanid) where RN=1;
Cursor 2 -
CURSOR C2 IS
select APPID, MESSAGE, MESSAGEDATE
from (select T1.HORIGINATIONS$APPLICATIONID APPID, T2.MESSAGE MESSAGE, T2.OCCURENCETIMESTAMP MESSAGEDATE, row_number()
over (partition by T1.HORIGINATIONS$APPLICATIONID order by T2.OCCURENCETIMESTAMP desc) ROW_NUMBER
FROM HORG_ODS.HORIGINA$VERIFICATION_APPLICAT T1,
HORG_ODS.HORIGINATION$APPLICATIONMESSAG T2
WHERE T2.ID = T1.HORIGINATION$APPLICATIONMESSAG
AND T2.CODE = 'OAM:0152'
and T1.HORIGINATIONS$APPLICATIONID = VARC1.APPID) r
where r.ROW_NUMBER=1;
VARC10 C2%ROWTYPE;
Thanks,
Bhola