title: Best way to run Oracle queries periodically
body: <p>I need to know what the best practice is regarding running a query periodically on Oracle (I'm using <strong>11g</strong>).</p>
  
  <p>In my particular use case I have a <code>DUE_DATE</code> specified in table <code>x</code>. 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 <code>x.DUE_DATE</code> and some user-specified values for what signifies 'warn' and 'critical' (contained within table <code>y</code>).</p>
  
  <ul>
  <li>OK --> <code>today &lt; x.DUE_DATE - y.WARN</code></li>
  <li>Warn --> <code>today &gt;= x.DUE_DATE - y.WARN and today &lt; x.DUE_DATE - y.CRITICAL</code> </li>
  <li>Critical --> <code>today &gt;= x.DUE_DATE - y.CRITICAL and today &lt;= x.DUE_DATE</code> </li>
  <li>Overdue --> <code>today &gt; x.DUE_DATE</code></li>
  </ul>
  
  <p>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:</p>
  
  <ul>
  <li><a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6002.htm" rel="nofollow">Materialized Views</a></li>
  <li><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#CIHHBGGI" rel="nofollow"><code>DBMS_SCHEDULER</code></a></li>
  </ul>
  
  <p>I know that I could just calculate the status dynamically upon every user request but as stauses 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.</p>
  
  <p>Many thanks in advance.</p>