使用 Materialized View 來同步資料

Materialized View  用途可以分成 1.用來提高查詢的效能 2.同步資料、傳輸資料

依照Materialized View 的特性可以先把繁重的查詢結果預先存在真實的Table上，在做Report 時則能快速的產出

Materialized View  利用dblink ，則可以做到資料傳輸、同步資料的效果



以下是 甲地 >乙地 做同步資料的案例

1.在來源端建立 Materialized View Log ，紀錄異動時的資訊

1. create materialized view log on mv_test
2. with rowid
3. including new values;

2.在目的端建立可異動的 Materialized View (也可建立只能查詢的 MV)

1. create materialized view mv_target refresh fast on demand with rowid for update
2. as select * from mv_test@dblink  

3.進行資料同步 (F：快速刷新、C：完全刷新)

1. begin
2.          dbms_mview.refresh('test.mv_target ','F');
3. end;

4.這樣目的端的 Materialized View  就有了來源端的資料囉



以下是 甲地 <>乙地 雙向同步資料的案例


1.在甲乙兩地先建立Table

1. create table test   (
2.     id varchar2(10) not null primary key,  
3.     name varchar2(20),  
4.     status varchar2(1),  
5.     updatetime date  );

2.在甲地與乙地的 Table 上建立 Materialized View Log ，紀錄異動時的資訊

1. create materialized view log on test
2. with rowid
3. including new values;

3.在甲地建立 Materialized View

1. create materialized view mv_testA refresh fast on demand with rowid    
2. as select * from test@dblink_to_B  

4.在乙地建立 Materialized View

1. create materialized view mv_testB refresh fast on demand with rowid    
2. as select * from test@dblink_to_A

5.在兩地建立 Procedures 來同步Table (非常簡單的同步只有insert、update)

1. CREATE OR REPLACE PROCEDURE MV_update 
2. IS
3.    v_count   NUMBER;
4. 
   
5. BEGIN
6.    DBMS_MVIEW.refresh ('MV_TESTB', 'F');
7.    SELECT COUNT (*) into v_count
8.      FROM MV_TESTB ;
9. 
   
10.    IF v_count > 0    THEN
11.       MERGE INTO test a
12.            USING (SELECT id,  name,   status,  updatetime 
13.                     FROM MV_TESTB) b
14.               ON (a.id = b.id)
15.       WHEN MATCHED
16.       THEN
17.          UPDATE SET
18.             a.name = b.name, a.status = b.status, a.updatetime = b.updatetime 
19.          where a.updatetime < b.updatetime 
20.       WHEN NOT MATCHED
21.       THEN
22.          INSERT  (a.id, a.name, a.status, a.updatetime ) VALUES (b.id, b.name, b.status, b.updatetime );
23.       COMMIT;
24.    END IF;
25. END;
26. /

6.執行PROCEDURE  看看是否成功　

1. BEGIN 
2.   VINCENTYUAN.MV_UPDATE;
3.   COMMIT; 
4. END; 

Create Materialized View 時可以選擇更新模式有分成以下

更新模式
1.fast -- 只更新被異動的部份 , 等於 merge into
2.Complete --完全更新 , 等於 delete from mv 在insert all
3.Force --會先判斷能否以fast更新要是不能的話, 會用complete 模式更新

更新方式
1. on demand --使用者自己手動刷新 或者 使用排程刷新
2. on commit --即時更新 , 來源端有異動即更新 (有相關限制)


建立定期更新的 Materialized View

1. create materialized view m_t2 refresh complete on demand 
2. with primary key
3. start with to_date('2014-08-05 11:00:00', 'yyyy-mm-dd hh24:mi:ss') /* first execute time  */
4. next sysdate+1/24  /* next execute time , 表示一小時執行一次*/
5. as select * from test2;

注意事項

1.來源端的Table 要是被進行 table move tablespace 或者 shrink space 類似rowid 會變動時，來源端 Materialized View log 內的異動資訊會被清除，目的端Materialized View 要更新資料時就必須先使用 C模式整個刷新 (只有 with row id 才會, with primary key 不會 )

2.遇到原Table 結構修改例如 新增欄位... Materialized View 就必須刪除重建..不能修改

相關授權

 grant select on test to test;    
 grant create table to test;
 grant select on mlog$_test to test;
 grant create materialized view to test;
 grant on commit refresh to test;

延伸閱讀

Oracle DB 有一個功能更是可以把 Materialized View  特性發揮的淋漓盡致，叫 Query rewrite

我們都知道 Materialized View 可以預先把繁重的查詢先儲存在一個Table 裡，使用Query rewrite後就不用修改前端的SQL，CBO並能自動的使用Materialized View 內的資訊，我覺得這算是用空間來換取執行速度


使用這麼好的功能前必須
1.使用CBO
 因為使用CBO 所以必須 Analyze 相關 Table、MV、Index。

2.Query_rewrite_enabled 參數必須為  TRUE

1. SQL> ALTER SESSION SET query_rewrite_enabled=TRUE;

QUERY_REWRITE相關參數

查詢 : SHOW PARAMETER QUERY_REWRITE

1. SQL> SHOW PARAMETER QUERY_REWRITE
2. NAME                                 TYPE        VALUE
3. ------------------------------------ ----------- ------------------------
4. query_rewrite_enabled                string       TRUE
5. query_rewrite_integrity              string      TRUSTED

可以看到有兩個參數影響著QUERY_REWRITE，第一個參數只有兩個（true/false）功能開跟關

第二個參數query_rewrite_integrity ，Oracle 官方解釋如下
       
ENFORCED
   Oracle enforces and guarantees consistency and integrity


TRUSTED
   Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.

以上兩個參數都是要求MV裡的數據必須是最新的

STALE_TOLERATED
   Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.

可以容忍MV內的數據是舊的，意思是查詢出來的資料可能會不準確


以下是 Query rewrite 簡單示範

先看看 Query rewrite 建立前的執行計畫

1. select owner,count(owner)  from TEST group by owner; 

建立具有 Query rewrite 的MV 

1. create materialized view mid_table enable query rewrite
2. as
3. select owner,count(owner)  from test group by owner; 

看看建立後的執行計畫，在執行原有的SQL時，CBO會去自動去查詢 Materialized View

1. select owner,count(owner)  from TEST group by owner; 

cost 從 12366 大幅下降到 3

無使用此技術前，前端還必須修改Sql，有了此技術的後是，程式工程師不必再修改前端的SQL，便能使速度加快



-------
建立 MATERIALIZED VIEW PARTITION 定時更新


1.
CREATE MATERIALIZED VIEW LOG ON TEST
WITH PRIMARY KEY
INCLUDING NEW VALUES;

2.
CREATE MATERIALIZED VIEW TEST
   COMPRESS FOR OLTP
   PARTITION BY RANGE
      (TIME)
      INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
      (PARTITION 201611 VALUES LESS THAN (TIMESTAMP ' 2016-11-01 00:00:00'))
   REFRESH FAST WITH PRIMARY KEY START WITH SYSDATE NEXT SYSDATE + 5 / 1440 FOR UPDATE
AS SELECT * FROM TEST@DBLINK;