Oracle Database 的 Materialized View 建立

在 Oracle Database 中,

針對查詢需要很久時間的資料,

Oracle Database 還可利用 Materialized View

 建立 "不會自動更新資料" 的 Materialized View 語法
CREATE MATERIALIZED VIEW Material_View_Name
REFRESH [COMPLETE / FORCE (預設) / FAST]
[WITH ROWID / WITH PRIMARY KEY (預設)]
AS
SELECT SQL

 建立 "只要 Table Commit, 就會自動更新資料" 的 Materialized View 語法
CREATE MATERIALIZED VIEW Material_View_Name
REFRESH [COMPLETE / FORCE (預設) / FAST]
ON COMMIT
[WITH ROWID / WITH PRIMARY KEY (預設)]
AS
SELECT SQL

 建立 "定期自動更新資料" 的 Materialized View 語法
CREATE MATERIALIZED VIEW Material_View_Name
REFRESH [COMPLETE / FORCE (預設) / FAST]
START WITH SYSDATE
NEXT (SYSDATE+1)
[WITH ROWID / WITH PRIMARY KEY (預設)]
AS
SELECT SQL

 "手動刷新" Materialized View 資料的語法
BEGIN
DBMS_MVIEW.REFRESH( 'Material_View_Name' );
END;

 查詢 Materialized View 狀態的語法
SELECT MVIEW_NAME
, REFRESH_METHOD
, LAST_REFRESH_TYPE
, TO_CHAR(LAST_REFRESH_DATE, 'YYYYMMDD HH24:MI:SS') LAST_REFRESH_DATE
, QUERY
FROM SYS.DBA_MVIEWS;

天聖經:神的心不僅在神的聖言當中,也在神所創造的萬物之中,天涯海角每一處都有神的心
Related Posts Plugin for WordPress, Blogger...