oracle - 刷新现有物化 View

Question

我使用以下信息创建了一个物化 View 。

CREATE MATERIALIZED VIEW EMPLOYEE_INFO
AS
SELECT * FROM EMPLOYEE_TABLE WHERE LOCATION = 'Brazil'

我最初没有给这个MV添加任何刷新间隔。现在，我需要每天0000点刷新这个MV。以下命令可以帮助我将其更改为每天 0000HRS 吗？

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
REFRESH COMPLETE 
START WITH SYSDATE

如果我需要每 6 小时刷新一次，我该如何执行？可能吗？

Answer 1

对于定期刷新，您必须使用 NEXT 子句。每天 00:00 刷新:

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
 REFRESH COMPLETE
 NEXT TRUNC(SYSDATE) + 1

每 6 小时刷新一次:

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
 REFRESH COMPLETE
 NEXT SYSDATE + 6/24

来自文档 ( ALTER MATERIALIZED VIEW ):

START WITH Clause

Specify START WITH date to indicate a date for the first automatic refresh time.

NEXT Clause

Specify NEXT to indicate a date expression for calculating the interval between automatic refreshes.

Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then Oracle Database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then Oracle Database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the alter_mv_refresh entirely, then Oracle Database does not automatically refresh the materialized view.

在下一次自动刷新时，Oracle数据库刷新物化 View ，计算NEXT表达式以确定下一次自动刷新时间，并继续自动刷新。