概念：
物化檢視是一種特殊的物理表，“物化”(Materialized)檢視是相對普通檢視而言的。普通檢視是虛擬表，應用的侷限性大，任何對檢視的查詢，Oracle都實際上轉換為檢視SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。

物化檢視型別：
包含聚集的物化檢視；只包含連線的物化檢視；巢狀物化檢視。三種物化檢視的快速重新整理的限制條件有很大區別，而對於其他方面則區別不大。

建立物化檢視時可以指定多種選項，下面對幾種主要的選擇進行簡單說明：

建立方式（Build Methods）：
包括BUILD IMMEDIATE和BUILD DEFERRED兩種。BUILD IMMEDIATE是在建立物化檢視的時候就生成資料，而BUILD DEFERRED則在建立時不生成資料，以後根據需要在生成資料。預設為BUILD IMMEDIATE。

查詢重寫（Query Rewrite）：
包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種。分別指出建立的物化檢視是否支援查詢重寫。查詢重寫是指當對物化檢視的基表進行查詢時，Oracle會自動判斷能否透過查詢物化檢視來得到結果，如果可以，則避免了聚集或連線操作，而直接從已經計算好的物化檢視中讀取資料。預設為DISABLE QUERY REWRITE。

重新整理（Refresh）：
指當基表發生了DML操作後，物化檢視何時採用哪種方式和基表進行同步。

重新整理的模式有兩種：
ON DEMAND和ON COMMIT。ON DEMAND指物化檢視在使用者需要的時候進行重新整理，可以手工透過DBMS_MVIEW.REFRESH等方法來進行重新整理，也可以透過JOB定時進行重新整理。 ON COMMIT指出物化檢視在對基表的DML操作提交的同時進行重新整理。

重新整理的方法有四種：
FAST、COMPLETE、FORCE和NEVER。FAST重新整理採用增量重新整理，只重新整理自上次重新整理以後進行的修改。COMPLETE重新整理對整個物化檢視進行完全的重新整理。如果選擇FORCE方式，則Oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則採用FAST方式，否則採用COMPLETE的方式。NEVER指物化檢視不進行任何重新整理。預設值是FORCE ON DEMAND。

物化檢視日誌：
如果需要進行快速重新整理，則需要建立物化檢視日誌。物化檢視日誌根據不同物化檢視的快速重新整理的需要，可以建立為ROWID或PRIMARY KEY型別的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。物化檢視日誌表的名稱為mlog$_後面跟基表的名稱，如果表名的長度超過20位，則只取前20位，當截短後出現名稱重複時，oracle會自動在物化檢視日誌名稱後面加上數字作為序號。

物化檢視維護操作：
1. 建立語句：create materialized view mv_name as select * from table_name 預設情況下，如果沒指定重新整理方法和重新整理模式，則Oracle預設為FORCE和DEMAND。
2. 建立ON COMMIT物化檢視：create materialized view mv_name refresh force on commit as select * from table_name 備註：實際建立過程中，基表需要有主鍵約束，否則會報錯（ORA-12014）
3. 建立定時重新整理的物化檢視：create materialized view mv_name refresh force on demand start with sysdate next sysdate+1 （指定物化檢視每天重新整理一次）
4. 指定重新整理時間（比如每天晚上10：00定時重新整理一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
5. 對於已經建立好的物化檢視，可以修改其重新整理方式，比如把物化檢視mv_name的重新整理方式修改為每天晚上10點重新整理一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
6. 物化檢視的刪除：drop materialized view mv_name

實驗操作流程：（同庫或者異庫遷移同步表）

流程介紹：該方法的實現原理是對於要遷移的表物件，需要有一個主鍵，用於mv的重新整理，對於符合該要求的表，在源表上建立mv日誌，再在目標資料庫上建立結構一樣的表，然後在目標表上採用prebuilt方式建立mv，第一次採用完全重新整理，之後採用增量重新整理，等真正要切換的時候，只需要重新整理完增量的日誌，刪除mv，保留目標表即可。

說明：這裡我用同庫的兩個使用者代替異庫操作， scott使用者代表源庫， andy使用者代表目標庫。

1.在源庫上建立表和mview log

SQL> create table andy_01 as select * from dba_objects ;
Table created.
SQL> select count(*) from andy_01;

COUNT(*)
----------
88770
--為基表建立主鍵，防止ORA-12014 does not contain a primary key constraint
SQL> delete from andy_01 where object_id is null;
SQL> alter table andy_01 add constraint pk_andy_01 primary key(object_id);
SQL> create materialized view log on andy_01;
Materialized view log created.
--源庫查詢生成 materialized view log 物件名
SQL> select object_name from user_objects where object_name like '%MLOG$%';
OBJECT_NAME
-----------------------------------------------------------------------------
I_MLOG$_ANDY_01
MLOG$_ANDY_01 -> materialized view log 物件名

2. 授權

-- 授權目標庫使用者有查詢源庫 materialized view log 的許可權
SQL> grant select on scott.MLOG$_ANDY_01 to andy;
Grant succeeded.
SQL> grant select on scott.andy_01 to andy;

Grant succeeded.

3.在目標資料庫上建立與該表一樣的表，並在該表上建立prebuilt mv

SQL> create table andy_01 as select * from scott.andy_01 where 1=2; 異庫加上@dblink_name
Table created.
SQL> select count(*) from andy_01;
COUNT(*)
----------
0
-- sys使用者為目標使用者授權
SQL> GRANT CREATE MATERIALIZED VIEW TO andy;

Grant succeeded.
-- 目標庫建立 materialized view
SQL> create materialized view andy_01 on prebuilt table refresh fast as select * from scott.andy_01;
Materialized view created.

4.做完全重新整理和增量重新整理

SQL> exec dbms_mview.refresh('andy_01','Complete');
PL/SQL procedure successfully completed.
SQL> select count(*) from andy_01;

COUNT(*)
----------
88765

--此時模擬在做完全重新整理過程中，源庫的表又發生了變化
SQL> insert into andy_01(object_id,owner) values(666666,'test');

1 row created.
SQL> commit;

Commit complete.
--再做增量重新整理
SQL> select count(*) from andy_01;

COUNT(*)
----------
88765
SQL> exec dbms_mview.refresh('andy_01');
PL/SQL procedure successfully completed.

SQL> select count(1) from andy_01;

COUNT(1)
----------
88766

5.停機切換,做最後一次重新整理，然後刪除源庫的mview log和目標庫的mview

SQL> exec dbms_mview.refresh('andy_01');
PL/SQL procedure successfully completed.

6. 遷移完畢，清除 materialized view 與 materialized view log

-- 清除目標庫 materialized view
SQL> drop materialized view andy_01;
Materialized view dropped.
SQL> select count(*) from andy_01;
COUNT(*)
----------
88766
這裡刪除的mview(andy_01)是prebuilt mv，所以刪除該mview,並不刪除相應的表。
-- 清除源庫 materialized view log
SQL> drop materialized view log on ANDY_01;

Materialized view log dropped.

SQL> select object_name from user_objects where object_name like '%MLOG$%';

no rows selected

物化檢視基礎概念、mview跨庫遷移表

相關文章