先来再次分析一下物化视图日志的结构。
yang@rac1>create table t (id number ,name varchar2(30),val number);
Table created.
yang@rac1>create materialized view log on t with rowid,sequence (id,name) including new values;
Materialized view log created.
yang@rac1>desc mlog$_t
Name Null? Type
------------------------------ -------
ID NUMBER
NAME VARCHAR2(30)
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER
ID，NAME 记录表中的字段的值
M_ROW$$ 记录变更字段所在列的rowid
SEQUENCE$$ 当某一行数据多次发生变更，记录变更的顺序。
SNAPTIME$$ --这篇文章将要讲述的字段
DMLTYPE$$
OLD_NEW$$
CHANGE_VECTOR$$ --变更矢量。定位变更的字段位置。
XID$$
从物化视图日志的结构可以得知当刷新物化视图时，只需要根据SEQUENCE$$列给出的顺序，通过M_ROW$$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$$定位到字段，然后根据基表中的数据重复执行DML操作。
如果物化视图日志只针对一个物化视图，那么刷新过程很简单，按照上面分析的执行就可以，oracle自己会将物化视图日志记录清除掉。但是对于多个基于同一个表的物化视图的快速刷新是怎样的呢？物化视图在刷新时还必须判断哪些物化视图日志记录是当前物化视图刷新需要的，哪些是不需要的。而且，物化视图还必须确定，在刷新物化视图后，物化视图日志中哪些记录是需要清除的，
哪些是不需要清除的。这时日志表中的SNAPTIME$$ 和user_mviews，LAST_REFRESH_DATE就起到作用了，通过这两个字段就是来标识什么情况下要刷新和清除日志的。
下面我们通过一个实验来展示一下多个基于同一个表的物化视图的刷新过程。
yang@rac1>create materialized view mv_t_id refresh fast as select id ,count(1) from t group by id;
Materialized view created.
yang@rac1>create materialized view mv_t_name refresh fast as select name ,count(1) from t group by name;
Materialized view created.
yang@rac1>create materialized view mv_t_idna refresh fast as select id,name ,count(1) from t group by id,name;
Materialized view created.
yang@rac1>insert into t values(1,'a',1);
1 row created.
yang@rac1>insert into t values(2,'b',2);
1 row created.
yang@rac1>insert into t values(3,'lily',3);
1 row created.
yang@rac1>insert into t values(4,'yang',4);
1 row created.
yang@rac1>update t set name ='yang' where id=2;
1 row updated.
yang@rac1>delete t where id=4;
1 row deleted.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ------ ------------------------- --------------------- -
1 a AAAgOPAAOAAA9AOAAA Jan 01 4000 00:00:00 I
2 b AAAgOPAAOAAA9AOAAB Jan 01 4000 00:00:00 I
3 lily AAAgOPAAOAAA9AOAAC Jan 01 4000 00:00:00 I
4 yang AAAgOPAAOAAA9AOAAD Jan 01 4000 00:00:00 I
2 b AAAgOPAAOAAA9AOAAB Jan 01 4000 00:00:00 U
2 yang AAAgOPAAOAAA9AOAAB Jan 01 4000 00:00:00 U
4 yang AAAgOPAAOAAA9AOAAD Jan 01 4000 00:00:00 D
7 rows selected.
从视图user_mview_refresh_times来看所有物化视图上次被刷新的时间。
yang@rac1>select name ,last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -----------------------------
MV_T_NAME Apr 12 2011 09:22:27
MV_T_ID Apr 12 2011 09:22:02
MV_T_IDNA Apr 12 2011 09:23:02
6 rows selected.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:22:28 FRESH
MV_T_ID Apr 12 2011 09:22:02 FRESH
MV_T_IDNA Apr 12 2011 09:23:02 FRESH
yang@rac1>insert into t values(5,'LILY',5);
1 row created.
yang@rac1>COMMIT;
Commit complete.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:22:28 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:22:02 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:23:02 NEEDS_COMPILE
刷新所有物化视图。
yang@rac1>exec dbms_mview.refresh('MV_T_NAME');
PL/SQL procedure successfully completed.
yang@rac1>exec dbms_mview.refresh('MV_T_ID');
PL/SQL procedure successfully completed.
yang@rac1>exec dbms_mview.refresh('MV_T_IDNA');
PL/SQL procedure successfully completed.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -----------
MV_T_NAME Apr 12 2011 09:29:50 FRESH
MV_T_ID Apr 12 2011 09:29:55 FRESH
MV_T_IDNA Apr 12 2011 09:30:03 FRESH
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
no rows selected
可见刷新所有基于日志的物化视图后，物化视图日志被自动清除。当仅仅刷新某个物化视图的情况
yang@rac1>insert into t values(6,'LILY',6);
1 row created.
yang@rac1>commit;
Commit complete.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ------ ------------------ ---------------------- -
6 LILY AAAgOPAAOAAA9AOAAF Jan 01 4000 00:00:00 I
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:29:50 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:29:55 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:30:03 NEEDS_COMPILE
所有基于表T的物化视图都显示为NEEDS_COMPILE。
yang@rac1>exec dbms_mview.refresh('MV_T_IDNA');
PL/SQL procedure successfully completed.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:29:50 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:29:55 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:33:45 FRESH
注意 MV_T_IDNA的 最新刷新时间改变了。查看物化日志的snaptime$$,也被更新为刷新的时间了。
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
-- ------ -------------------- -------------------- -
6 LILY AAAgOPAAOAAA9AOAAF Apr 12 2011 09:33:45 I
再次刷新一个物化视图。查看物化日志的snaptime$$,并未更改。
yang@rac1>exec dbms_mview.refresh('MV_T_ID');
PL/SQL procedure successfully completed.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
-- ------ -------------------- -------------------- -
6 LILY AAAgOPAAOAAA9AOAAF Apr 12 2011 09:33:45 I
在user_mviews中last_refresh_date小于mlog$_t中的snaptime$$的值的都标记为needs_compile；现在基本可以看出多个物化视图基于同一个表的情况下，物化视图刷新的条件
当每个物化视图对应的LAST_REFRESH_DATE小于物化日志的snaptime$$的值时，oracle会认为此物化视图需要刷新，当然反之，则认为已经刷新过了。
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:29:50 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:34:30 FRESH
MV_T_IDNA Apr 12 2011 09:33:45 FRESH
物化视图日志的管理是由oracle自动管理的。当所有基于某个表的物化视图被刷新后，oracle会自动删除已经执行过刷新的记录。
yang@rac1>insert into t values(6,'yang',6);
1 row created.
yang@rac1>commit;
Commit complete.
yang@rac1>exec dbms_mview.refresh('MV_T_NAME');
PL/SQL procedure successfully completed.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ------------------------------ ------------------------- ----------------------------- -
6 yang AAAgOPAAOAAA9AOAAD Apr 12 2011 09:36:17 I
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_FACT Apr 12 2011 09:28:08 FRESH
MV_T_NAME Apr 12 2011 09:36:17 FRESH
MV_T_ID Apr 12 2011 09:34:30 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:33:45 NEEDS_COMPILE
yang@rac1>exec dbms_mview.refresh('MV_T_ID');
PL/SQL procedure successfully completed.
yang@rac1>exec dbms_mview.refresh('MV_T_IDNA');
PL/SQL procedure successfully completed.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
no rows selected
可以看出日志中的所有记录被清除。
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_FACT Apr 12 2011 09:28:08 FRESH
MV_T_NAME Apr 12 2011 09:36:17 FRESH
MV_T_ID Apr 12 2011 09:53:34 FRESH
MV_T_IDNA Apr 12 2011 09:53:37 FRESH
最后，简单总结一下：
物化视图在刷新时，会刷新所有 SNAPTIME$$大于本物化视图上次刷新时间的记录，并将所有是4000-01-01 00:00:00的记录更新为当前刷新时间。对于其他大于上次刷新时间的记录，只刷新不更改。这样，当刷新执行完以后，数据字典中记录当前物化视图的上次刷新时间为当前时刻，这保证了物化视图日志中目前所有的记录都小于或等于刷新时间。因此，每个物化视图只要刷新大于上次刷新时间的记录，且保证每次刷新后，所有记录的时间都小于等于上次刷新时间，那么无论有多少个物化视图，就可以互不影响的使用同一个物化视图日志进行快速刷新。当物化视图刷新完之后，会清除那些SNAPTIME$$列小于所有物化视图的上次刷新时间的记录。
yang@rac1>create table t (id number ,name varchar2(30),val number);
Table created.
yang@rac1>create materialized view log on t with rowid,sequence (id,name) including new values;
Materialized view log created.
yang@rac1>desc mlog$_t
Name Null? Type
------------------------------ -------
ID NUMBER
NAME VARCHAR2(30)
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER
ID，NAME 记录表中的字段的值
M_ROW$$ 记录变更字段所在列的rowid
SEQUENCE$$ 当某一行数据多次发生变更，记录变更的顺序。
SNAPTIME$$ --这篇文章将要讲述的字段
DMLTYPE$$
OLD_NEW$$
CHANGE_VECTOR$$ --变更矢量。定位变更的字段位置。
XID$$
从物化视图日志的结构可以得知当刷新物化视图时，只需要根据SEQUENCE$$列给出的顺序，通过M_ROW$$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$$定位到字段，然后根据基表中的数据重复执行DML操作。
如果物化视图日志只针对一个物化视图，那么刷新过程很简单，按照上面分析的执行就可以，oracle自己会将物化视图日志记录清除掉。但是对于多个基于同一个表的物化视图的快速刷新是怎样的呢？物化视图在刷新时还必须判断哪些物化视图日志记录是当前物化视图刷新需要的，哪些是不需要的。而且，物化视图还必须确定，在刷新物化视图后，物化视图日志中哪些记录是需要清除的，
哪些是不需要清除的。这时日志表中的SNAPTIME$$ 和user_mviews，LAST_REFRESH_DATE就起到作用了，通过这两个字段就是来标识什么情况下要刷新和清除日志的。
下面我们通过一个实验来展示一下多个基于同一个表的物化视图的刷新过程。
yang@rac1>create materialized view mv_t_id refresh fast as select id ,count(1) from t group by id;
Materialized view created.
yang@rac1>create materialized view mv_t_name refresh fast as select name ,count(1) from t group by name;
Materialized view created.
yang@rac1>create materialized view mv_t_idna refresh fast as select id,name ,count(1) from t group by id,name;
Materialized view created.
yang@rac1>insert into t values(1,'a',1);
1 row created.
yang@rac1>insert into t values(2,'b',2);
1 row created.
yang@rac1>insert into t values(3,'lily',3);
1 row created.
yang@rac1>insert into t values(4,'yang',4);
1 row created.
yang@rac1>update t set name ='yang' where id=2;
1 row updated.
yang@rac1>delete t where id=4;
1 row deleted.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ------ ------------------------- --------------------- -
1 a AAAgOPAAOAAA9AOAAA Jan 01 4000 00:00:00 I
2 b AAAgOPAAOAAA9AOAAB Jan 01 4000 00:00:00 I
3 lily AAAgOPAAOAAA9AOAAC Jan 01 4000 00:00:00 I
4 yang AAAgOPAAOAAA9AOAAD Jan 01 4000 00:00:00 I
2 b AAAgOPAAOAAA9AOAAB Jan 01 4000 00:00:00 U
2 yang AAAgOPAAOAAA9AOAAB Jan 01 4000 00:00:00 U
4 yang AAAgOPAAOAAA9AOAAD Jan 01 4000 00:00:00 D
7 rows selected.
从视图user_mview_refresh_times来看所有物化视图上次被刷新的时间。
yang@rac1>select name ,last_refresh from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -----------------------------
MV_T_NAME Apr 12 2011 09:22:27
MV_T_ID Apr 12 2011 09:22:02
MV_T_IDNA Apr 12 2011 09:23:02
6 rows selected.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:22:28 FRESH
MV_T_ID Apr 12 2011 09:22:02 FRESH
MV_T_IDNA Apr 12 2011 09:23:02 FRESH
yang@rac1>insert into t values(5,'LILY',5);
1 row created.
yang@rac1>COMMIT;
Commit complete.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:22:28 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:22:02 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:23:02 NEEDS_COMPILE
刷新所有物化视图。
yang@rac1>exec dbms_mview.refresh('MV_T_NAME');
PL/SQL procedure successfully completed.
yang@rac1>exec dbms_mview.refresh('MV_T_ID');
PL/SQL procedure successfully completed.
yang@rac1>exec dbms_mview.refresh('MV_T_IDNA');
PL/SQL procedure successfully completed.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -----------
MV_T_NAME Apr 12 2011 09:29:50 FRESH
MV_T_ID Apr 12 2011 09:29:55 FRESH
MV_T_IDNA Apr 12 2011 09:30:03 FRESH
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
no rows selected
可见刷新所有基于日志的物化视图后，物化视图日志被自动清除。当仅仅刷新某个物化视图的情况
yang@rac1>insert into t values(6,'LILY',6);
1 row created.
yang@rac1>commit;
Commit complete.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ------ ------------------ ---------------------- -
6 LILY AAAgOPAAOAAA9AOAAF Jan 01 4000 00:00:00 I
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:29:50 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:29:55 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:30:03 NEEDS_COMPILE
所有基于表T的物化视图都显示为NEEDS_COMPILE。
yang@rac1>exec dbms_mview.refresh('MV_T_IDNA');
PL/SQL procedure successfully completed.
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:29:50 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:29:55 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:33:45 FRESH
注意 MV_T_IDNA的 最新刷新时间改变了。查看物化日志的snaptime$$,也被更新为刷新的时间了。
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
-- ------ -------------------- -------------------- -
6 LILY AAAgOPAAOAAA9AOAAF Apr 12 2011 09:33:45 I
再次刷新一个物化视图。查看物化日志的snaptime$$,并未更改。
yang@rac1>exec dbms_mview.refresh('MV_T_ID');
PL/SQL procedure successfully completed.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
-- ------ -------------------- -------------------- -
6 LILY AAAgOPAAOAAA9AOAAF Apr 12 2011 09:33:45 I
在user_mviews中last_refresh_date小于mlog$_t中的snaptime$$的值的都标记为needs_compile；现在基本可以看出多个物化视图基于同一个表的情况下，物化视图刷新的条件
当每个物化视图对应的LAST_REFRESH_DATE小于物化日志的snaptime$$的值时，oracle会认为此物化视图需要刷新，当然反之，则认为已经刷新过了。
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_T_NAME Apr 12 2011 09:29:50 NEEDS_COMPILE
MV_T_ID Apr 12 2011 09:34:30 FRESH
MV_T_IDNA Apr 12 2011 09:33:45 FRESH
物化视图日志的管理是由oracle自动管理的。当所有基于某个表的物化视图被刷新后，oracle会自动删除已经执行过刷新的记录。
yang@rac1>insert into t values(6,'yang',6);
1 row created.
yang@rac1>commit;
Commit complete.
yang@rac1>exec dbms_mview.refresh('MV_T_NAME');
PL/SQL procedure successfully completed.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ------------------------------ ------------------------- ----------------------------- -
6 yang AAAgOPAAOAAA9AOAAD Apr 12 2011 09:36:17 I
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_FACT Apr 12 2011 09:28:08 FRESH
MV_T_NAME Apr 12 2011 09:36:17 FRESH
MV_T_ID Apr 12 2011 09:34:30 NEEDS_COMPILE
MV_T_IDNA Apr 12 2011 09:33:45 NEEDS_COMPILE
yang@rac1>exec dbms_mview.refresh('MV_T_ID');
PL/SQL procedure successfully completed.
yang@rac1>exec dbms_mview.refresh('MV_T_IDNA');
PL/SQL procedure successfully completed.
yang@rac1>select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
no rows selected
可以看出日志中的所有记录被清除。
yang@rac1>select mview_name,last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ----------------------------- -------------------
MV_FACT Apr 12 2011 09:28:08 FRESH
MV_T_NAME Apr 12 2011 09:36:17 FRESH
MV_T_ID Apr 12 2011 09:53:34 FRESH
MV_T_IDNA Apr 12 2011 09:53:37 FRESH
最后，简单总结一下：
物化视图在刷新时，会刷新所有 SNAPTIME$$大于本物化视图上次刷新时间的记录，并将所有是4000-01-01 00:00:00的记录更新为当前刷新时间。对于其他大于上次刷新时间的记录，只刷新不更改。这样，当刷新执行完以后，数据字典中记录当前物化视图的上次刷新时间为当前时刻，这保证了物化视图日志中目前所有的记录都小于或等于刷新时间。因此，每个物化视图只要刷新大于上次刷新时间的记录，且保证每次刷新后，所有记录的时间都小于等于上次刷新时间，那么无论有多少个物化视图，就可以互不影响的使用同一个物化视图日志进行快速刷新。当物化视图刷新完之后，会清除那些SNAPTIME$$列小于所有物化视图的上次刷新时间的记录。
