【kingsql分享】深入研究Oracle物化檢視 之 保證資料一致的奧祕
1.如果是跨庫的物化檢視，且這些伺服器系統時間都不相同，Oracle怎麼保證資料的一致性呢？
2.Oracle通過時間列來判斷哪些記錄需要重新整理，而Oracle的SYSDATE時間是來自系統時間的，那麼如果系統時間改變，Oracle如何保證重新整理了之後不會丟失資料，如何保證不會重複重新整理呢？
於是在2013年夏天的某個週六（記憶中），我設計這個實驗驗證了Oracle的強大！
1.調整伺服器時間
設定兩個伺服器，紅和藍，紅色為基表所在和一個物化檢視，藍色為跨庫的物化檢視，調整其時間必須不一樣，最好是差別特別的大。
HZH@MASTER> select sysdate from dual;
SYSDATE
-------------------
2013-06-29 17:30:58
06:25:05 SQL> select sysdate from dual;
SYSDATE
-------------------
2014-06-30 06:25:14
2.建立基表和物化檢視
HZH@MASTER> create table test (id number primary key);
Table created.
HZH@MASTER> create materialized view log on test;
Materialized view log created.
HZH@MASTER> create materialized view mv_test_on_master refresh fast as select * from test;
Materialized view created.
06:25:10 SQL> create public database link to_master connect to hzh identified by hzh using 'MASTER';
Database link created.
06:25:14 SQL> create materialized view mv_test_on_client refresh fast
06:25:43 2 as select * from test@to_master;
Materialized view created.
3.插入記錄
HZH@MASTER> insert into test values (1);
1 row created.
HZH@MASTER> commit;
Commit complete.
4.檢視物化檢視日誌
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:40:26 1 4000-01-01 00:00:00 I N FE
5.重新整理紅色伺服器的物化檢視
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:41:03 1 2013-06-29 17:40:58 I N FE
HZH@MASTER> select MVIEW_NAME,LAST_REFRESH_DATE,STALENESS from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58 FRESH
HZH@MASTER> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
這一切看起來都是那麼的正常，沒有什麼問題，繼續往下看
6.檢視藍色伺服器的物化檢視
它的最後重新整理時間應該是建立物化檢視的時間，由輸出來分析，user_mviews的時間是2014-06-30 06:25:49，返回建立的時候，敲回車之前是06:25:43，也就是說user_mviews所記錄的時間是藍色伺服器建立物化檢視時候的系統時間（藍色系統時間）；然而再看user_mview_refresh_times，它的時間是2013-06-29 17:38:08，推測，可能是建立物化檢視的時候，紅色伺服器所記錄的系統時間（紅色時間），紅色時間的判定，後面會驗證！
06:35:05 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:25:49
06:35:07 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:38:08
7.再插入一條記錄
HZH@MASTER> insert into test values (2);
1 row created.
HZH@MASTER> commit;
Commit complete.
8.重新整理藍色物化檢視
06:38:46 SQL> exec dbms_mview.refresh('mv_test_on_client','f');
PL/SQL procedure successfully completed.
06:38:50 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:38:50
這個時候就可以通過計算來驗證，user_mview_refresh_times的最後重新整理時間，是不是紅色伺服器當時的系統時間，
2014-06-30 06:25:49對應2013-06-29 17:38:08
2014-06-30 06:38:50對應2013-06-29 17:51:08
9.檢視物化檢視日誌
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:52:08 2 2013-06-29 17:51:08 I N FE
這裡需要詳細進行說明：藍色物化檢視重新整理的時間，是2014-06-30 06:38:50，而主伺服器記錄到物化檢視日誌中的SNAPTIME$$欄位的卻是紅色伺服器自己的時間！
設想一下，如果物化檢視日誌中的SNAPTIME$$欄位是2014-06-30 06:38:50，會發生什麼樣的情況，也就是如下虛擬出來的情況
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:52:08 2 2014-06-30 06:38:50 I N FE
這樣，紅色的物化檢視重新整理時間為2013-06-29 17:40:58，如下輸出所示
HZH@PROD> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
假設現在的時間是2013-06-29 19:40:58，我們重新整理了紅色物化檢視，id為2的記錄被重新整理，紅色物化檢視的最後重新整理時間會變成2013-06-29 19:40:58，而物化檢視日誌中的時間是不會變的！這樣當2013-06-29 21:40:58再重新整理的時候，因為SNAPTIME$$列的值還是大於2013-06-29 19:40:58，還會重新整理一次（重複重新整理），而使得物化檢視的最後重新整理時間變為2013-06-29 21:40:58，由於還是小於那個2014年，所以下次重新整理的時候，還會重新整理（多次重複重新整理）！然而正常的情況應該是，紅色的重新整理一次之後，物化檢視日誌的那條記錄就應該清空！
所以，Oracle的開發人員使用瞭如下的方法記錄SNAPTIME$$
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:52:08 2 2013-06-29 17:51:08 I N FE
當紅色伺服器重新整理自己的物化檢視時，日誌中這條記錄應該是被更新，然後最後的重新整理時間會變成系統時間，大於SNAPTIME$$的值，然而SNAPTIME$$的值小於所有物化檢視的最後重新整理時間，則日誌這條記錄會被清空！稍後第11步再驗證！
10.再插入記錄並重新整理
HZH@MASTER> insert into test values (3);
1 row created.
HZH@MASTER> commit;
Commit complete.
06:41:20 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:41:20
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:54:20 2 2013-06-29 17:51:08 I N FE
2013-06-29 17:54:20 3 2013-06-29 17:53:39 I N FE
HZH@MASTER> insert into test values (100);
1 row created.
HZH@MASTER> commit;
Commit complete.
HZH@MASTER> select sysdate from dual;
SYSDATE
-------------------
2013-06-29 17:57:14
2秒後重新整理物化檢視
06:41:33 SQL> exec dbms_mview.refresh('mv_test_on_client','f');
PL/SQL procedure successfully completed.
//此時主伺服器時間約為2013-06-29 17:57:14 + 1至2秒=2013-06-29 17:57:16，即主伺服器認為2013-06-29 17:57:16，藍色的物化檢視進行了重新整理！
06:44:58 SQL> select mview_name,last_refresh_date from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE
------------------------------ -------------------
MV_TEST_ON_CLIENT 2014-06-30 06:44:57
06:45:35 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
HZH@MASTER> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 17:58:24 100 2013-06-29 17:57:16 I N FE
2013-06-29 17:58:24 2 2013-06-29 17:51:08 I N FE
2013-06-29 17:58:24 3 2013-06-29 17:53:39 I N FE
11.驗證剛才第9步最後所說的那段話
物化檢視日誌如下
HZH@PROD> select sysdate,mlog$_test.* from mlog$_test;
SYSDATE ID SNAPTIME$$ D O CHANGE_VECTOR$$
------------------- ---------- ------------------- - - --------------------
2013-06-29 18:50:34 100 2013-06-29 17:57:16 I N FE
2013-06-29 18:50:34 2 2013-06-29 17:51:08 I N FE
2013-06-29 18:50:34 3 2013-06-29 17:53:39 I N FE
從主伺服器角度看時間
HZH@PROD> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
06:57:18 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
HZH@PROD> select name,mview_last_refresh_time
2 from user_base_table_mviews a, user_registered_mviews b
3 where a.mview_id = b.mview_id;
NAME MVIEW_LAST_REFRESH_
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 17:40:58
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
紅色物化檢視重新整理之後，id為2，3，100的都將被重新整理，然後更改紅色物化檢視重新整理時間為當前sysdate=2013-06-29 18:53:53，然後，以上三條記錄的SNAPTIME$$（2013-06-29 17:57:16，2013-06-29 17:51:08，2013-06-29 17:53:39）都小於等於所有物化檢視的最後重新整理時間（2013-06-29 18:53:53，2013-06-29 17:57:16），所以日誌中的三條記錄將全被清空！
18:53:36 HZH@PROD> exec dbms_mview.refresh('mv_test_on_master','f');
PL/SQL procedure successfully completed.
18:53:53 HZH@PROD> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 18:53:53
18:54:14 HZH@PROD> select name,mview_last_refresh_time
18:54:28 2 from user_base_table_mviews a, user_registered_mviews b
18:54:34 3 where a.mview_id = b.mview_id;
NAME MVIEW_LAST_REFRESH_
------------------------------ -------------------
MV_TEST_ON_MASTER 2013-06-29 18:53:53
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
07:07:05 SQL> select name, last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_TEST_ON_CLIENT 2013-06-29 17:57:16
驗證日誌是否全被清空
19:01:25 HZH@PROD> select sysdate,mlog$_test.* from mlog$_test;
no rows selected
kingsql分享
hongzhuohui@kingsql.com
2014-10-10
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1294749/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 物化檢視案例分享Oracle
- 資料庫的物化檢視資料庫
- Oracle資料庫中物化檢視的原理剖析Oracle資料庫
- oracle物化檢視Oracle
- oracle 建立物化檢視Oracle
- Oracle 物化檢視建立Oracle
- 資料複製_物化檢視
- 【kingsql分享】檢視metadata的語句SQL
- ORACLE中的物化檢視建立Oracle
- 【ORACLE】物化檢視相關後設資料檢視欄位說明Oracle
- (轉)oracle資料庫之間 表複製方法二（物化檢視 方法）Oracle資料庫
- Oracle普通檢視和物化檢視的區別Oracle
- 小議物化檢視與基表資料不一致的消除（四）
- 小議物化檢視與基表資料不一致的消除（二）
- 小議物化檢視與基表資料不一致的消除（一）
- 小議物化檢視與基表資料不一致的消除（三）
- Oracle物化檢視詳解Oracle
- ORACLE物化檢視測試Oracle
- Oracle物化檢視語法Oracle
- ORACLE物化檢視入門Oracle
- oracle物化檢視系列(二)Oracle
- oracle物化檢視系列(一)Oracle
- 物化檢視--資料倉儲手冊
- CUUG oracle物化檢視講解Oracle
- Oracle物化檢視3 - Prebuilt MVOracleUI
- Oracle 物化檢視 例項一Oracle
- Oracle物化檢視及SnapshotOracle
- 不同Oracle版本下物化檢視註釋的不同之處Oracle
- 【檢視】oracle 資料字典檢視之 DICT / DICTIONARYOracle
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- ORACLE之檢視資料庫的SQLOracle資料庫SQL
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- Oracle物化檢視的建立及使用（二）Oracle
- Oracle物化檢視的建立及使用（一）Oracle
- 【kingsql分享】邏輯資料庫結構 之 資料塊SQL資料庫
- DB2資料庫物化檢視：MQT物化查詢表的使用DB2資料庫MQQT
- 物化檢視如何快速完成資料聚合操作？
- [zt]prebuilt 物化檢視遷移資料庫UI資料庫