oracle 创建物化视图，每天刷新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22481099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Materialized view which refresh records on daily
提问by cool_taps
Currently the Materialized view which I had created using REFRESH ON DEMAND so in this case I need to refresh MV explicitly using below command:
目前我使用 REFRESH ON DEMAND 创建的物化视图，因此在这种情况下，我需要使用以下命令显式刷新 MV：
BEGIN DBMS_MVIEW.REFRESH('MV_DATA'); END;
But now I need to refresh this MV on daily basis so could anyone please help to write this. I have seen that we can refresh this MV using writing explicit Job or using COMPLETE/FAST REFRESH statement in MV itself.
但是现在我需要每天刷新这个MV，所以有人可以帮忙写这个。我已经看到我们可以使用编写显式 Job 或在 MV 本身中使用 COMPLETE/FAST REFRESH 语句来刷新此 MV。
Thanks in advance!
提前致谢！
回答by San
You need to create the materialized view using START WITH and NEXT Clause
您需要使用 START WITH 和 NEXT Clause 创建物化视图
create materialized view <mview_name>
refresh on demand
start with sysdate next sysdate + 1
as select ............
So if you want to refresh mview daily, you need to keep it refresh on demand and set the next refresh time as sysdate + 1. You can set any interval although.
所以如果你想每天刷新mview，你需要保持按需刷新，并将下次刷新时间设置为sysdate + 1。您可以设置任何间隔。
Once you do this the materialized view is created and a job is set in Oracle that will refresh mview every 24 hrs (sysdate + 1).
完成此操作后，将创建物化视图并在 Oracle 中设置一个作业，该作业将每 24 小时刷新一次 mview (sysdate + 1)。
For more information on how to do that, follow this link
回答by Sarath Avanavu
If you simply need a SQL query to simply refresh at 12 AM, then the below query would be enough.
如果您只需要一个 SQL 查询来简单地刷新 at 12 AM，那么下面的查询就足够了。
CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDIATE
REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM <YOUR TABLE>
If you need to have it refreshed around 6 AM, then use the below script. You can see and additional logic as + 6 / 24. In case if you need to change to 4 AM, use the logic as + 4 / 24.
如果您需要刷新它6 AM，请使用以下脚本。您可以将其他逻辑视为+ 6 / 24. 如果您需要更改为4 AM，请使用逻辑为+ 4 / 24。
CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDEATE
REFRESH FAST START WITH (SYSDATE) NEXT (SYSDATE + 1) + 6 / 24 WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM <YOUR TABLE>
回答by Dominic Pajares
I have edited Sarath's Script for it to run on specific time (i.e. 6AM).
我已经编辑了 Sarath 的脚本，让它在特定时间（即早上 6 点）运行。
CREATE MATERIALIZED VIEW MV_DATA
BUILD IMMEDEATE
REFRESH FAST START WITH (SYSDATE) NEXT (TRUNC(SYSDATE) + 1) + 6 / 24 WITH ROWID
ON COMMIT
DISABLE QUERY REWRITE
AS SELECT * FROM YOURTABLE
回答by Tarkeshwar Prasad
Refresh the mv every day at 1 AM
每天凌晨1点刷新mv
CREATE MATERIALIZED VIEW test1 BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (trunc(sysdate)+1)+1/24 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE "Your query"
CREATE MATERIALIZED VIEW test1 BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (trunc(sysdate)+1)+1/24 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
