一．物化视图日志格式说明
Oracle 物化视图说明
http://www.cndba.cn/Dave/article/1260
Oracle 物化视图 详细错误描述 查看方法
http://www.cndba.cn/Dave/article/1091
Oracle 物化视图 快速刷新 限制 说明
http://www.cndba.cn/Dave/article/1090
Oracle 的物化视图的快速刷新要求必须建立物化视图日志，通过物化视图日志可以实现增量刷新功能。
官网的说明如下：
http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN107
A materializedview log is required on a master to perform a fast refresh on materializedviews based on the master. When you create a materialized view log for a mastertable or master materialized view, Oracle creates an underlyingtable as the materialized view log. A materialized viewlog can hold the primary keys, rowids, or object identifiers of rows, or both,that have been updated in the master table or master materialized view.A materialized view log can also contain other columns to support fastrefreshes of materialized views with subqueries.
--物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECTID几种类型，同时还可以指定SEQUENCE或明确指定列名。不过上面这些情况产生的物化视图日志的结构都不相同。
The name of amaterialized view log's table is MLOG$_master_name. The materialized viewlog is created in the same schema as the target master. One materialized viewlog can support multiple materialized views on its master table or mastermaterialized view. As described in the previoussection, the internal trigger adds change information to the materialized viewlog whenever a DML transaction has taken place on the target master.
--这里要注意，当发生DML 操作时，内部的触发器会把变化记录到物化视图日志里，也就是说物化视图不支持DDL的同步，所以在物化视图的编写过程中不可使用select * from 的形式，因为这样当基表发生变化时，物化视图就会失效。
物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。
虽然物化视图格式会有不同，但任何物化视图都会包括如下列：
--这是一个primarykey的物化视图日志：
SQL> desc MLOG$_BS_CHNGROUP_REL
Name Null? Type
------------------------------------------------- ------------
GROUP_ID VARCHAR2(10)
PARENT_GROUP_ID VARCHAR2(10)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER
相关解释如下：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。
CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。
NSERT和DELETE操作都是记录集的，即INSERT和DELETE会影响整条记录。而UPDATE操作是字段集的，UPDATE操作可能会更新整条记录的所有字段，也可能只更新个别字段。
无论从性能上考虑还是从数据的一致性上考虑，物化视图刷新时都应该是基于字段集。Oracle就是通过CHANGE_VECTOR$$列来记录每条记录发生变化的字段包括哪些。
基于主键、ROWID和OBJECT ID的物化视图日志在CHANGE_VECTOR$$上略有不同，但是总体设计的思路是一致的。
CHANGE_VECTOR$$列是RAW类型，其实Oracle采用的方式就是用每个BIT位去映射一个列。
比如：第一列被更新设置为02，即00000010。第二列设置为04，即00000100，第三列设置为08，即00001000。当第一列和第二列同时被更新，则设置为06，00000110。如果三列都被更新，设置为0E，00001110。
依此类推，第4列被更新时为10，第5列20，第6列40，第7列80，第8列0001。当第1000列被更新时，CHANGE_VECTOR$$的长度为1000/4+2为252。
除了可以表示UPDATE的字段，还可以表示INSERT和DELETE。DELETE操作CHANGE_VECTOR$$列为全0，具体个数由基表的列数决定。INSERT操作的最低位为FE如果基表列数较多，而存在高位的话，所有的高位都为FF。如果INSERT操作是前面讨论过的由UPDATE操作更新了主键造成的，则这个INSERT操作对应的CHANGE_VECTOR$$列为全FF。
如果WITH后面跟了ROWID，则物化视图日志中会包含：
M_ROW$$：用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID，则物化视图日志中会包含：
SYS_NC_OID$：用来记录每个变化对象的对象ID。
如果WITH后面跟了SEQUENCE，则物化视图日子中会包含：
SEQUENCE$$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。
二．根据物化视图日志来快速刷新数据过程
2.1 创建测试环境
SQL> create table t (id number, namevarchar2(30), num number);
Table created.
SQL> create materialized view log on twith rowid, sequence (id, name) including new values ;
Materialized view log created.
SQL> 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是建立物化视图日志时指定的基表中的列，它们记录每次DML操作对应的ID和NAME的值。
M_ROW$$保存基表的ROWID信息，根据M_ROW$$中的信息可以定位到发生DML操作的记录。
SEQUENCE$$根据DML操作发生的顺序记录序列的编号，当刷新时，根据SEQUENCE$$中的顺序就可以和基表中的执行顺序保持一致。
SNAPTIME$$列记录了刷新操作的时间。
DMLTYPE$$的记录值I、U和D，表示操作是INSERT、UPDATE还是DELETE。
OLD_NEW$$表示物化视图日志中保存的信息是DML操作之前的值（旧值）还是DML操作之后的值（新值）。除了O和N这两种类型外，对于UPDATE操作，还可能表示为U。
CHANGE_VECTOR$$记录DML操作发生在那个或那几个字段上
当刷新物化视图时，只需要根据SEQUENCE$$列给出的顺序，通过M_ROW$$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$$定位到字段，然后根据基表中的数据重复执行DML操作。
如果物化视图日志只针对一个物化视图，那么刷新过程就是这么简单，还需要做的不过是在刷新之后将物化视图日志清除掉。
但是，Oracle的物化视图日志是可以同时支持多个物化视图的快速刷新的，也就是说，物化视图在刷新时还必须判断哪些物化视图日志记录是当前物化视图刷新需要的，哪些是不需要的。而且，物化视图还必须确定，在刷新物化视图后，物化视图日志中哪些记录是需要清除的，哪些是不需要清除的。
回顾一下物化视图日志的结构，发现只剩下一个SHAPTIME$$列，那么Oracle如何仅通过这一列就完成了对多个物化视图的支持呢？下面建立一个小例子，通过例子来进行说明。
2.2 测试说明
使用上文中建立的表和物化视图日志，下面对这个表建立三个快速刷新的物化视图。
SQL> create materialized view mv_t_idrefresh fast as
2 select id, count(*) from t group by id;
实体化视图已创建。
SQL> create materialized view mv_t_namerefresh fast as
2 select name, count(*) from t group by name;
实体化视图已创建。
SQL> create materialized viewmv_t_id_name refresh fast as
2 select id, name, count(*) from t group by id, name;
实体化视图已创建。
SQL> insert into t values (1, 'a', 2);
已创建 1 行。
SQL> insert into t values (1, 'b', 3);
已创建 1 行。
SQL> insert into t values (2, 'a', 5);
已创建 1 行。
SQL> insert into t values (3, 'b', 7);
已创建 1 行。
SQL> update t set name = 'c' where id =3;
已更新 1 行。
SQL> delete t where id = 2;
已删除 1 行。
SQL> select id, name, m_row$$,snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ---------- ------------------ ------------------- -
1a AAACJEAAFAAAAD4AAA4000-01-01 00:00:00 I
1b AAACJEAAFAAAAD4AAB4000-01-01 00:00:00 I
2a AAACJEAAFAAAAD4AAC4000-01-01 00:00:00 I
3b AAACJEAAFAAAAD4AAD4000-01-01 00:00:00 I
3b AAACJEAAFAAAAD4AAD4000-01-01 00:00:00 U
3c AAACJEAAFAAAAD4AAD4000-01-01 00:00:00 U
2a AAACJEAAFAAAAD4AAC4000-01-01 00:00:00 D
已选择7行。
当发生了DML操作后，物化视图日志中的SNAPTIME$$列保持的值是4000-01-01 00:00:00。这个值表示这条记录还没有被任何物化视图刷新过。第一个刷新这些记录的物化视图会将SNAPTIME$$的值更新为物化视图当前的刷新时间。
SQL> exec dbms_mview.refresh('MV_T_ID')
PL/SQL 过程已成功完成。
SQL> select id, name, m_row$$,snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ---------- ------------------ ------------------- -
1a AAACJEAAFAAAAD4AAA2005-03-06 00:56:59 I
1b AAACJEAAFAAAAD4AAB2005-03-06 00:56:59 I
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
3c AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 D
已选择7行。
Oracle根据数据字典中的信息可以知道表T上建立了三个物化视图，因此，MV_T_ID刷新完之后，不会删除物化视图记录。
Oracle的数据字典中还保存着每个物化视图上次刷新的时间和当前的刷新状态。
SQL> select name, last_refresh fromuser_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
MV_T_ID 2005-03-06 00:56:59
MV_T_ID_NAME 2005-03-06 00:46:09
MV_T_NAME 2005-03-06 00:46:04
SQL> select mview_name,last_refresh_date, staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T_ID 2005-03-06 00:56:59 FRESH
MV_T_ID_NAME 2005-03-06 00:46:09 NEEDS_COMPILE
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE
这些视图中记录了每个物化视图上次执行刷新操作的时间，并且给出每个物化视图中的数据是否是和基表同步的。由于MV_T_ID刚刚进行了刷新，因此状态是FRESH，而另外两个由于在刷新（建立）之后，基表又进行了DML操作，因此状态为NEEDS_COMPILE。如果这时对基表进行DML操作，则MV_T_ID的状态也会变为NEEDS_COMPILE。
SQL> insert into t values (4, 'd', 10);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select id, name, m_row$$,snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ---------- ------------------ ------------------- -
1a AAACJEAAFAAAAD4AAA2005-03-06 00:56:59 I
1b AAACJEAAFAAAAD4AAB2005-03-06 00:56:59 I
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
3c AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 D
4d AAACJEAAFAAAAD4AAE4000-01-01 00:00:00 I
已选择8行。
SQL> select mview_name,last_refresh_date, staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T_ID 2005-03-06 00:56:59 NEEDS_COMPILE
MV_T_ID_NAME 2005-03-06 00:46:09 NEEDS_COMPILE
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE
下面刷新物化视图MV_T_ID_NAME，刷新操作的判断依据是，只刷新SNAPTIME$$列大于当前物化视图的LAST_REFRESH_DATE的记录，由于物化视图日志中所有记录的SNAPTIME$$的值都比物化视图MV_T_ID_NAME上次刷新的时间点大，因此会刷新所有记录。对于SNAPTIME$$列的值是4000-01-0100:00:00的记录，物化视图会把SNAPTIME$$列的值更新为当前刷新时间，对于那些已经被更新过的SNAPTIME$$列，则保持原值。
SQL> execdbms_mview.refresh('MV_T_ID_NAME')
PL/SQL 过程已成功完成。
SQL> select id, name, m_row$$,snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ---------- ------------------ ------------------- -
1a AAACJEAAFAAAAD4AAA2005-03-06 00:56:59 I
1b AAACJEAAFAAAAD4AAB2005-03-06 00:56:59 I
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
3c AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 D
4d AAACJEAAFAAAAD4AAE2005-03-06 01:18:22 I
已选择8行。
SQL> select mview_name,last_refresh_date, staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T_ID 2005-03-06 00:56:59 NEEDS_COMPILE
MV_T_ID_NAME 2005-03-06 01:18:22 FRESH
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE
如果这时再次刷新物化视图MV_T_ID，则只有ID=4的这条记录的SNAPTIME$$的时间点大于MV_T_ID上次刷新的时间点，因此，只刷新这一条记录，且不会改变SNAPTIME$$的值。
SQL> exec dbms_mview.refresh('MV_T_ID')
PL/SQL 过程已成功完成。
SQL> select id, name, m_row$$,snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ---------- ------------------ ------------------- -
1a AAACJEAAFAAAAD4AAA2005-03-06 00:56:59 I
1b AAACJEAAFAAAAD4AAB2005-03-06 00:56:59 I
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 I
3b AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
3c AAACJEAAFAAAAD4AAD2005-03-06 00:56:59 U
2a AAACJEAAFAAAAD4AAC2005-03-06 00:56:59 D
4d AAACJEAAFAAAAD4AAE2005-03-06 01:18:22 I
已选择8行。
SQL> select mview_name, last_refresh_date,staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T_ID 2005-03-06 01:25:30 FRESH
MV_T_ID_NAME 2005-03-06 01:18:22 FRESH
MV_T_NAME 2005-03-06 00:46:04 NEEDS_COMPILE
到目前为止，还没有看到过物化视图日志的清除，其实每次进行完刷新，物化视图日志都会试图删除没有用的物化视图日志记录。物化视图日志记录的删除条件是删除那些SNAPTIME$$列小于等于基表所有物化视图的上次刷新时间。在上面的例子中，由于MV_T_NAME一直没有刷新，因此它的LAST_REFRESH_DATE比物化视图日志中所有记录的值都小，因此，一直没有发生物化视图日志记录清除的现象。
SQL> insert into t values (5, 'e', 2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> execdbms_mview.refresh('MV_T_NAME')
PL/SQL 过程已成功完成。
SQL> select id, name, m_row$$,snaptime$$, dmltype$$ from mlog$_t;
ID NAME M_ROW$$ SNAPTIME$$ D
---------- ---------- ------------------ ------------------- -
5e AAACJEAAFAAAAD4AAF2005-03-06 01:31:33 I
SQL> select mview_name,last_refresh_date, staleness from user_mviews;
MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T_ID 2005-03-06 01:25:30 NEEDS_COMPILE
MV_T_ID_NAME 2005-03-06 01:18:22 NEEDS_COMPILE
MV_T_NAME 2005-03-06 01:31:33 FRESH
物化视图MV_T_NAME刷新了物化视图中的每条记录，更新了ID=5的记录的SNAPTIME$$时间，并清除了其它所有物化视图日志记录。
总结：
物化视图在刷新时，会刷新所有SNAPTIME$$大于本物化视图上次刷新时间的记录，并将所有是4000-01-01 00:00:00的记录更新为当前刷新时间。对于其他大于上次刷新时间的记录，只刷新不更改。这样，当刷新执行完以后，数据字典中记录当前物化视图的上次刷新时间为当前时刻，这保证了物化视图日志中目前所有的记录都小于或等于刷新时间。因此，每个物化视图只要刷新大于上次刷新时间的记录，且保证每次刷新后，所有记录的时间都小于等于上次刷新时间，那么无论有多少个物化视图，就可以互不影响的使用同一个物化视图日志进行快速刷新了。当物化视图刷新完之后，会清除那些SNAPTIME$$列小于所有物化视图的上次刷新时间的记录，而这些记录已经被所有的物化视图都刷新过了，保存在物化视图日志中已经没有意义了。
三．物化视图日志被多个物化视图使用时
当物化视图日志被多个物化视图使用时，不同的物化视图使用不同的刷新间隔，会导致mv log中记录过多而使得快速刷新变得缓慢。
3.1 创建基表和物化视图日志
SQL> create table dave as select * fromall_objects where object_id<10000;
Table created.
SQL> alter table dave add primarykey(object_id);
Table altered.
SQL> create materialized viewlog on dave with primary key;
Materialized view log created.
3.2 创建on commit物化视图
SQL> create materialized viewmv_commit
2 refresh fast
3 on commit
4 as
5 select object_id,object_type,object_name
6 from dave;
Materialized view created.
3.3 创建on demand 物化视图
SQL> create materialized view mv_demand
2 refresh fast
3 on demand
4 as
5 selectobject_id,LAST_DDL_TIME,STATUS
6 from master;
Materialized view created.
3.4查看目前mv和mv log中的数据量
--刷新视图：
SQL> exec dbms_mview.refresh('mv_commit','f');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_demand','f');
PL/SQL procedure successfully completed.
SQL> select count(*) from mv_commit;
COUNT(*)
----------
4552
SQL> select count(*) from mv_demand;
COUNT(*)
----------
4552
SQL> select count(*) from MLOG$_DAVE;
COUNT(*)
----------
0
3.5往dave表插入新的记录并commit
SQL> insert into dave select * fromall_objects where object_id>10000 and object_id < 20000;
7695 rows created.
SQL> commit;
3.6查看mv和mv log中的数据量
SQL> select count(*) from mv_commit;
COUNT(*)
----------
12247
SQL> select count(*) from mv_demand;
COUNT(*)
----------
4552
SQL> select count(*) from MLOG$_DAVE;
COUNT(*)
----------
7695
注意：这里的log 表+mv_demand == mv_commit;
3.7再往dave表插入新的记录并commit
SQL> insert into dave select * fromall_objects where object_id>20000 and object_id < 21000;
999 rows created.
SQL> commit;
Commit complete.
3.8 再次查看
SQL> select count(*) from mv_commit;
COUNT(*)
----------
13246
SQL> select count(*) from mv_demand;
COUNT(*)
----------
4552
SQL> select count(*) from MLOG$_DAVE;
COUNT(*)
----------
8694
注意：这里的log 表+mv_demand == mv_commit;
随着dave不停的插入新的数据，mv_commit不停的被刷新，但是mv log中的记录了却没有在mv_commit刷新后清空，而是不停的增长。这是由于mv log中的数据对于mv_demand来说还是必须的，这样就导致mv_commit的刷新会越来越慢，因为快速刷新每次都是根据mv log中的记录来执行刷新的。
3.9手动刷新一次mv_demand
SQL> execdbms_mview.refresh('mv_demand','f');
PL/SQL procedure successfully completed.
SQL> select count(*) from mv_commit;
COUNT(*)
----------
13246
SQL> select count(*) from mv_demand;
COUNT(*)
----------
13246
SQL> select count(*) from MLOG$_DAVE;
COUNT(*)
----------
8694
SQL> exec dbms_mview.refresh('mv_demand','f');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('mv_commit','f');
PL/SQL procedure successfully completed.
SQL> select count(*) from MLOG$_DAVE;
COUNT(*)
----------
0
物化视图日志被清空了。其实每次进行完刷新，物化视图日志都会试图删除没有用的物化视图日志记录。物化视图日志记录的删除条件是删除那些SNAPTIME$$列小于等于基表所有物化视图的上次刷新时间。
可以用SYS 用户执行如下SQL 来查看相关表上有多少个物化视图，已经最后一次刷新时间：
/* Formatted on 2012/7/6 1:05:32(QP5 v5.185.11230.41888) */
SELECT owner,
NAME,
snapshot_site,
TO_CHAR (current_snapshots, 'mm/dd/yyyy hh24:mi') current_snapshots
FROM dba_registered_snapshots, dba_snapshot_logs
WHERE dba_registered_snapshots.snapshot_id =dba_snapshot_logs.snapshot_id (+)
AND dba_snapshot_logs.MASTER=upper('&table_name');
OWNER NAME SNAPSHOT_SIT CURRENT_SNAPSHOT
-------- ---------- ----------------------------
ANQING MV_COMMIT DAVE 07/06/2012 01:01
ANQING MV_DEMAND DAVE 07/06/2012 01:01
注意：
文章整合自老杨的博客，链接如下：
http://blog.itpub.net/post/468/20498
http://yangtingkun.itpub.net/post/468/20584
-------------------------------------------------------------------------------------------------------
QQ:492913789
Email:ahdba@qq.com
Blog: http://www.cndba.cn/dave
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940
6817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
