定期运行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运行查询，以计算某些记录的状态(确定"，警告"，严重"或过期").特定记录的状态是根据相对于x.DUE_DATE的今天日期(其中今天"是运行查询的日期)以及表示警告"和严重"的一些用户指定值(包含在表中)计算得出的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).
- 确定->
today < x.DUE_DATE - y.WARN - 警告->
today >= x.DUE_DATE - y.WARN and today < x.DUE_DATE - y.CRITICAL - 严重->
today >= x.DUE_DATE - y.CRITICAL and today <= x.DUE_DATE - 逾期->
today > x.DUE_DATE
- 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:
我知道我可以根据每个用户的请求动态地计算状态，但是由于停滞状态每天只更改一次，所以我认为这样做也可以更高效，并且每天缓存一次结果.
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 是工具选择.因此，如果您要根据查询结果更新表中的状态，请使用
- 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; /
