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;

昭佑.天翔