I have a query which uses a series of functions to return the status of each document for a loan.
SELECT loan_number, borrower_name, get_application_status(loan_number, date_application_ordered, date_application_verified), get_appraisal_status(loan_number, date_appraisal_ordered, date_appraisal_verified), get_endorsement_status(loan_number, date_endorsement_ordered, date_endorsement_verified), get_waiver_status(loan_number, date_waiver_ordered, date_waiver_verified), get_insurance_status(loan_number, date_insurance_ordered, date_insurance_verified) FROM loans_table WHERE loan_type IN ('A', 'B', 'C') AND filing_date > TO_DATE('30-OCT-12') AND filing_date <= TO_DATE('13-NOV-12');
It returns several hundred rows and takes around 30 seconds. I suspect that it is taking so long because each function uses a subquery with a MAX function, like this:
FUNCTION get_waiver_status (loan_number IN NUMBER, date_ordered IN DATE, date_verified IN DATE) RETURN varchar2 AS last_upload_date DATE := NULL; BEGIN IF date_ordered IS NULL THEN RETURN 'Not Ordered'; END IF; SELECT MAX(upload_date) INTO last_upload_date FROM documents_table WHERE documents_table.loan_number = loan_number AND document_type = 'waiver'; IF last_upload_date >= date_verified THEN RETURN 'Not Verified Since Last Upload'; ELSE IF last_upload_date < date_verified THEN RETURN 'Verified'; END IF; END get_waiver_status;
How do I get the query to return faster? I have made indexes on loan_number and filing_date. I tried creating a trigger on documents_table to store the last upload_date of a document into a new table for the function to retrieve, but if a document is deleted, I need to be able to select the next latest upload_date from documents_table, which I can't do from within the trigger because I get a mutating table error. I am using PL/SQL on Oracle 10g R2.