I have a query which uses a series of functions to return the status of each document for a loan.

SELECT loan_number,
  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
last_upload_date DATE := NULL;
  IF date_ordered IS NULL THEN
    RETURN 'Not Ordered';
  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 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.

  • I make it arule to never write or use inline scalar user deifned functions. They are poor performers because they are essentially cursors. Stop writing this stuff and start using table valued functions if you must use functions. – HLGEM Nov 15 '12 at 22:04

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:

  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')

    loan.PK,                                   --- the Primary Key and
    loan.loan_type,                            --- all the other needed columns
    loan.date_waiver_verified ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.