How to speed up a query with a lot of subqueries

oracleoracle-10g-r2plsqlsubquery

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.

Best Answer

I guess that using subqueries inside functions is the main performance problem.

It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only:

SELECT 
  loan.loan_number,
  loan.borrower_name,
  ...
  CASE WHEN loan.date_waiver_ordered IS NULL 
         THEN 'Not Ordered'
       WHEN  MAX(doc.upload_date) >= loan.date_waiver_verified
         THEN 'Not Verified Since Last Upload'
       WHEN  MAX(doc.upload_date) < loan.date_waiver_verified
         THEN 'Verified'
       ELSE NULL
  END  AS get_waiver_status,
  ...

FROM loans_table AS loan

  LEFT JOIN documents_table AS doc             --- joining
    ON  doc.loan_number = loan.loan_number 
    AND doc.document_type = 'waiver' 

WHERE loan.loan_type IN ('A', 'B', 'C')
  AND loan.filing_date >= TO_DATE('30-OCT-12')
  AND loan.filing_date <  TO_DATE('13-NOV-12')

GROUP BY
    loan.PK,                                   --- the Primary Key and
    loan.loan_type,                            --- all the other needed columns
    loan.filing_date,
    loan.loan_number,
    loan.borrower_name,
    loan.date_waiver_verified ;
Related Question