定期运行 Oracle 查询的最佳方式

2021-12-30 00:00:00 oracle11g oracle

我需要知道在 Oracle 上定期运行查询的最佳实践是什么(我使用的是 11g).

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

在我的特定用例中,我在表 x 中指定了一个 DUE_DATE.我想要做的是每天00:01运行一个查询来计算一些记录的状态(OK、Warn、Critical或Overdue).相对于 x.DUE_DATE 和一些用户指定的表示警告"和'critical'(包含在表 y 中).

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

  • 好的 --> 今天 <x.DUE_DATE - y.WARN
  • Warn --> today >= x.DUE_DATE - y.WARN and today <x.DUE_DATE - y.CRITICAL
  • 关键 --> 今天 >= x.DUE_DATE - y.CRITICAL 和今天 <= x.DUE_DATE
  • 逾期 --> 今天 >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:

  • 物化视图
  • DBMS_SCHEDULER

我知道我可以根据每个用户请求动态计算状态,但由于状态每天只更改一次,我认为进行计算并每天缓存一次后续结果会更有效.

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.

非常感谢.

推荐答案

  • 用于运行作业(和查询)DBMS_SCHEDULER 是工具选择.因此,如果您想根据查询结果更新表中的状态,请使用 DBMS_SCHEDULER.
    • 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)
      ;
      

      要创建计划在每天 00:00 的作业:

      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.

相关文章