Best way to run Oracle queries periodically

Question

I need to know what the best practice is regarding running a query periodically on Oracle (I'm using 11g).

In my particular use case I have a DUE_DATE specified in table x. What I want to do is to run a query at 00:01 every day to calculate the status (OK, Warn, Critical or Overdue) of some records. The status of a particular record is calculated from today's date (where 'today' is the day the query is being run) relative to x.DUE_DATE and some user-specified values for what signifies 'warn' and 'critical' (contained within table y).

• OK --> today < x.DUE_DATE - y.WARN
• Warn --> today >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL
• Critical --> today >= x.DUE_DATE - y.CRITICAL and today <= x.DUE_DATE
• Overdue --> today > x.DUE_DATE

What is the best way of running this query periodically? I have found the following options but am not sure which is best for my use case:

• Materialized Views
• DBMS_SCHEDULER

I know that I could just calculate the status dynamically upon every user request but as statuses only change once a day I thought it would be more efficient to do the calculation and cache the subsequent result once a day too.

Answer 1

• For running jobs (and queries) DBMS_SCHEDULER is the tool to choose. So if you want to update the status in a table based on the results of your query, use DBMS_SCHEDULER.

For example you could schedule a job doing the following update:

update x
   set status = (CASE 
                   WHEN sysdate < x.DUE_DATE - y.WARN THEN
                     'Ok'
                   WHEN sysdate >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL THEN
                     'Warn'
                   WHEN sysdate >= x.DUE_DATE - y.CRITICAL and sysdate <= x.DUE_DATE THEN
                     'Critical'
                   WHEN sysdate > x.DUE_DATE THEN
                     'Overdue'
                 END)
;

To create the job scheduled daily at 00:00:

BEGIN
    dbms_scheduler.create_job(job_name => 'Status Updater',
                              job_type => 'PLSQL_BLOCK',
                              job_action => '
                                             BEGIN 
                                              update x
                                                set status = (CASE 
                                                                WHEN sysdate < x.DUE_DATE - y.WARN THEN
                                                                  ''Ok''
                                                                WHEN sysdate >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL THEN
                                                                  ''Warn''
                                                                WHEN sysdate >= x.DUE_DATE - y.CRITICAL and sysdate <= x.DUE_DATE THEN
                                                                  ''Critical''
                                                                WHEN sysdate > x.DUE_DATE THEN
                                                                  ''Overdue''
                                                              END)
                                                 ;
                                              END;',
                              start_date => systimestamp,
                              repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;',
                              enabled => TRUE);
END;
/

• If you need to prepare a report, either schedule the report in the reporting tool or use a Materialized View to store the result set.