定期运行 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.
- 如果您需要准备报告，请在报告工具中安排报告或使用物化视图 用于存储结果集.
- 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.
例如，您可以安排执行以下更新的作业:
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;
/
相关文章