定期运行 Oracle 查询的最佳方式 [英] Best way to run Oracle queries periodically

问题描述

我需要知道在 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.

这篇关于定期运行 Oracle 查询的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！