To Start with BI and Migrate to AI -點部落

table/View/Materialized View 比較

	是否落地	適用情境
TABLE	v
View			只有定義meta data未實際佔有space oracle 不需要重新 compile 執行計劃，執行計劃在create view的時候已經建置並儲存
Materialized View	v	1.用來提高查詢的效能 2.同步資料、傳輸資料	實際佔有space 有實體的資料；可以建置 index，當成一般Table使用

1：建立 "不會自動更新資料" 的 Materialized View 語法

CREATE MATERIALIZED VIEW Material_View_Name
[TABLESPACE TBS_XXX]
REFRESH [ON DEMAND (預設)/ COMMIT] 
        [COMPLETE / FORCE (預設) / FAST] 
        [WITH Primary Key (預設)/ ROWID / ROWID / OBJECT ID]
[START WITH SYSDATE NEXT (SYSDATE+1)]
AS
SELECT SQL

Refresh Interval
DEMAND: 分為自動跟手動，手動就是當需要refrest mview時執行 dbms_mview.refresh; 自動就是在create mview 時指定排程時間，會增加一個job在 dba_jobs定時refresh
COMMIT: master table 資料有異動的時候，transaction commit時 refresh mview。 (即時同步)

Refresh Type
COMPLETE: 每次refresh 時重新執行 mview Query 且重新建置 mview (費時)(文件中提到如果是 complete，mview 的 pctfree 會是0，pctused 會是 99，十分合理，因為這mview 每次都會重新建置，不需要留空間update使用)

如果來源資料表資料量大時，MATERIALIZED VIEW LOG (來源Table資料異動表，同步完成後即此異動表的資料會被刪除) 建議要建起來，在refresh時加快不少速度。

FAST: refresh mview 只更新 master 異動的資料 (較快但要建 mview log)
FORCE: refresh mview 時，會嘗試以 FAST 方式 refresh，如果無法完成則以 COMPLETE 方式 refresh

 

2.建立 "只要 Table Commit, 就會自動更新資料" 的 Materialized View 語法 

CREATE MATERIALIZED VIEW Material_View_Name
REFRESH [COMPLETE / FORCE (預設) / FAST]
ON COMMIT
[WITH ROWID / WITH PRIMARY KEY (預設)]
AS
SELECT SQL

3.建立 "定期自動更新資料" 的 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

4."手動刷新" Materialized View 資料的語法

BEGIN
  DBMS_MVIEW.REFRESH( 'Material_View_Name' );
END;

5.查詢 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;

 

mview 的類型:
# Primary Key Materialized Views
# Object Materialized Views
# ROWID Materialized Views
# Complex Materialized Views

不同的類型在資料refresh時有不同的限制；

例如:Complex Materialized Views refresh 時，會重新執行指定的Query，建議使用在資料量較小、SQL指令回應較快的需求上，

資料量大或SQL需較長時間的需求在refresh時所需資源也很可觀。

---

參考資料

1.使用 Materialized View 來同步資料

http://shingdong.blogspot.tw/2014/08/materialized-view.html

2.CREATE MATERIALIZED VIEW

http://py3939.pixnet.net/blog/post/25399529

3.Oracle Database 的 Materialized View 建立

http://tomkuo139.blogspot.tw/2010/01/oracle-database-materialized-view.html

4.《Materialized Views》

https://dotblogs.com.tw/sporting/2012/10/04/75263

5.《Materialized Views 專案實作經驗》

https://dotblogs.com.tw/sporting/2012/10/04/75264