October 2007 Archives
转载自： http://yangtingkun.itpub.net/post/468/20498
物化视图的快速刷新要求基本必须建立物化视图日志，这篇文章简单描述一下物化视图日志中各个字段的含义和用途。
物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。
物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECT ID几种类型，同时还可以指定SEQUENCE或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。
任何物化视图都会包括的列：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。
CHANGE_VECTOR$$表示修改矢量，用来表示被修改的是哪个或哪几个字段。
如果WITH后面跟了ROWID，则物化视图日志中会包含：
M_ROW$$：用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID，则物化视图日志中会包含：
SYS_NC_OID$：用来记录每个变化对象的对象ID。
如果WITH后面跟了SEQUENCE，则物化视图日子中会包含：
SEQUENCE$$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。
下面通过例子进行详细说明：
SQL> create table t_rowid (id number, name varchar2(30), num number);
表已创建。
SQL> create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
实体化视图日志已创建。
SQL> create table t_pk (id number primary key, name varchar2(30), num number);
表已创建。
SQL> create materialized view log on t_pk with primary key;
实体化视图日志已创建。
SQL> create type t_object as object (id number, name varchar2(30), num number);
2 /
类型已创建。
SQL> create table t_oid of t_object;
表已创建。
SQL> create materialized view log on t_oid with object id;
实体化视图日志已创建。
建立环境后来看看物化视图日志中包含的自动：
SQL> desc mlog$_t_rowid
名称 是否为空? 类型
--------------------------------------- -------- -------------
NAME VARCHAR2(30)
NUM NUMBER
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
除了最基本的4列之外，由于指定了ROWID、SEQUENCE和NAME、NUM列，因此物化视图日志中包含了相对应的列。
SQL> desc mlog$_t_pk
名称 是否为空? 类型
----------------------------------------- -------- -------------
ID NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
主键物化视图日志中除了基本列之外还包括基本的主键。
SQL> desc mlog$_t_oid
名称 是否为空? 类型
--------------------------------------- -------- -------------
SYS_NC_OID$ RAW(16)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
对象表的物化视图日志建立后包含系统对象标识列。
一、主键列、ROWID列、OBJECT ID列、SEQUENCE列和建立物化视图时指明的列。
主键、ROWID或OBJECT ID用来唯一表示物化视图日志中的记录。
SEQUENCE会根据操作发生的顺序对物化视图日志中的记录编号。
建立物化视图时指明的列会在物化视图日志中进行记录。
SQL> insert into t_rowid values (1, 'a', 5);
已创建 1 行。
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。
SQL> delete t_rowid;
已删除 1 行。
SQL> select name, num, m_row$$, sequence$$, dmltype$$ from mlog$_t_rowid;
NAME NUM M_ROW$$ SEQUENCE$$ D
---------- ---------- ------------------ ---------- -
a 5 AAACIDAAFAAAAD4AAA 70019 I
a 5 AAACIDAAFAAAAD4AAA 70020 U
c 5 AAACIDAAFAAAAD4AAA 70021 U
c 5 AAACIDAAFAAAAD4AAA 70022 D
SQL> insert into t_pk values (1, 'a', 5);
已创建 1 行。
SQL> update t_pk set name = 'c' where id = 1;
已更新 1 行。
SQL> delete t_pk;
已删除 1 行。
SQL> select id, dmltype$$ from mlog$_t_pk;
ID D
---------- -
1 I
1 U
1 D
SQL> insert into t_oid values (1, 'a', 5);
已创建 1 行。
SQL> update t_oid set name = 'c' where id = 1;
已更新 1 行。
SQL> delete t_oid;
已删除 1 行。
SQL> select sys_nc_oid$, dmltype$$ from mlog$_t_oid;
SYS_NC_OID$ D
-------------------------------- -
9F6DA94248EE40D5AB1E50700F9566EA I
9F6DA94248EE40D5AB1E50700F9566EA U
9F6DA94248EE40D5AB1E50700F9566EA D
SQL> rollback;
回退已完成。
二、时间列
当基本发生DML操作时，会记录到物化视图日志中，这时指定的时间4000年1月1日0时0分0秒。如果物化视图日志供多个物化视图使用，则一个物化视图刷新后会将它刷新的记录的时间更新为它刷新的时间。
下面建立快速刷新的两个物化视图来演示时间列的变化。（只有建立快速刷新的物化视图才能使用物化视图日志，如果只建立一个物化视图，则物化视图刷新完会将物化视图日志清除掉。
SQL> create materialized view mv_t_rowid refresh fast on commit as
2 select name, count(*) from t_rowid group by name;
实体化视图已创建。
SQL> create materialized view mv_t_rowid1 refresh fast as
2 select name, count(*) from t_rowid group by name;
实体化视图已创建。
SQL> insert into t_rowid values (1, 'a', 5);
已创建 1 行。
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。
SQL> delete t_rowid;
已删除 1 行。
SQL> select snaptime$$ from mlog$_t_rowid;
SNAPTIME$$
-------------------
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00
4000-01-01 00:00:00
SQL> commit;
提交完成。
SQL> select snaptime$$ from mlog$_t_rowid;
SNAPTIME$$
-------------------
2005-03-05 00:40:32
2005-03-05 00:40:32
2005-03-05 00:40:32
2005-03-05 00:40:32
COMMIT后，物化视图mv_t_rowid刷新，将SNAPTIME$$列更新成自己的刷新时间。
三、操作类型和新旧值
操作类型比较简单：只包括I(INSERT)、D(DELETE)和U(UPDATE)三种。
新旧值也包括三种：O表示旧值（一般对应的操作时DELETE）、N表示新值（一般对应的操作是INSERT），还有一种U（对应UPDATE操作）。
SQL> insert into t_pk values (1, 'a', 5);
已创建 1 行。
SQL> insert into t_pk values (2, 'b', 7);
已创建 1 行。
SQL> insert into t_pk values (3, 'c', 9);
已创建 1 行。
SQL> update t_pk set name = 'c' where id = 1;
已更新 1 行。
SQL> update t_pk set id = 4 where id = 2;
已更新 1 行。
SQL> delete t_pk where id = 3;
已删除 1 行。
SQL> select id, dmltype$$, old_new$$ from mlog$_t_pk;
ID D O
---------- - -
1 I N
2 I N
3 I N
1 U U
2 D O
4 I N
3 D O
已选择7行。
开始是插入三条记录，接着是UPDATE操作。需要注意，对于基于主键的物化视图日志，如果更新了主键，则UPDATE操作转化为一条DELETE操作，一条INSERT操作。最后是DELETE操作。
SQL> drop materialized view log on t_rowid;
实体化视图日志已删除。
SQL> create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
实体化视图日志已创建。
SQL> insert into t_rowid values (1, 'a', 5);
已创建 1 行。
SQL> insert into t_rowid values (2, 'b', 7);
已创建 1 行。
SQL> insert into t_rowid values (3, 'c', 9);
已创建 1 行。
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。
SQL> update t_rowid set id = 4 where id = 2;
已更新 1 行。
SQL> delete t_rowid where id = 3;
已删除 1 行。
SQL> select name, num, m_row$$, dmltype$$, old_new$$ from mlog$_t_rowid;
NAME NUM M_ROW$$ D O
---------- ---------- ------------------ - -
a 5 AAACIDAAFAAAAD4AAC I N
b 7 AAACIDAAFAAAAD4AAA I N
c 9 AAACIDAAFAAAAD4AAB I N
a 5 AAACIDAAFAAAAD4AAC U U
c 5 AAACIDAAFAAAAD4AAC U N
b 7 AAACIDAAFAAAAD4AAA U U
b 7 AAACIDAAFAAAAD4AAA U N
c 9 AAACIDAAFAAAAD4AAB D O
已选择8行。
查询结果和上面类似，唯一的区别是每条UPDATE操作都对应物化视图日志中的两条记录。一条对应UPDATE操作的原记录DMLTYPE$$和OLD_NEW$$都为U，一条对应UPDATE操作后的新记录，DMLTYPE$$为U，OLD_NEW$$为N。当建立物化视图日志时指出了INCLUDING NEW VALUES语句时，就会出现这种情况。
四、修改矢量
最后简单讨论一下CHANGE_VECTOR$$列。
INSERT和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。
SQL> insert into t_rowid values (1, 'a', 5);
已创建 1 行。
SQL> insert into t_rowid values (2, 'b', 7);
已创建 1 行。
SQL> insert into t_rowid values (3, 'c', 9);
已创建 1 行。
SQL> update t_rowid set name = 'c' where id = 1;
已更新 1 行。
SQL> update t_rowid set id = 4 where id = 2;
已更新 1 行。
SQL> update t_rowid set name = 'd', num = 11 where id = 3;
已更新 1 行。
SQL> delete t_rowid where id = 3;
已删除 1 行。
SQL> select name, num, m_row$$, dmltype$$, old_new$$, change_vector$$ from mlog$_t_rowid;
NAME NUM M_ROW$$ D O CHANGE_VEC
-------------------- ---------- ------------------ - - ----------
a 5 AAACIgAAFAAAAD4AAA I N FE
b 7 AAACIgAAFAAAAD4AAB I N FE
c 9 AAACIgAAFAAAAD4AAC I N FE
a 5 AAACIgAAFAAAAD4AAA U U 04
c 5 AAACIgAAFAAAAD4AAA U N 04
b 7 AAACIgAAFAAAAD4AAB U U 02
b 7 AAACIgAAFAAAAD4AAB U N 02
c 9 AAACIgAAFAAAAD4AAC U U 0C
d 11 AAACIgAAFAAAAD4AAC U N 0C
d 11 AAACIgAAFAAAAD4AAC D O 00
已选择10行。
可以看到，正如上面分析的，INSERT为FE，DELETE为00，对第一列的更新为02，第二列为04，第二列和第三列都更新为0C。需要注意，正常情况下，第一列会从02开始，但是如果对MLOG$表执行了TRUNCATE操作，或者重建了物化视图日志，则可能造成第一列开始位置发生偏移。
SQL> insert into t_pk values (1, 'a', 5);
已创建 1 行。
SQL> insert into t_pk values (2, 'b', 7);
已创建 1 行。
SQL> insert into t_pk values (3, 'c', 9);
已创建 1 行。
SQL> update t_pk set name = 'c' where id = 1;
已更新 1 行。
SQL> update t_pk set id = 4 where id = 2;
已更新 1 行。
SQL> delete t_pk where id = 1;
已删除 1 行。
SQL> select * from mlog$_t_pk;
ID SNAPTIME$$ D O CHANGE_VEC
---------- ------------------- - - ----------
1 4000-01-01 00:00:00 I N FE
2 4000-01-01 00:00:00 I N FE
3 4000-01-01 00:00:00 I N FE
1 4000-01-01 00:00:00 U U 04
2 4000-01-01 00:00:00 D O 00
4 4000-01-01 00:00:00 I N FF
1 4000-01-01 00:00:00 D O 00
已选择7行。
这个结果和ROWID类型基本一致，不同的是，如果更新了主键，会将UPDATE操作在物化视图日志中记录为一条DELETE和一条INSERT，不过这时INSERT对应的CHANGE_VECTOR$$的值是FF。
SQL> insert into t_oid values (1, 'a', 5);
已创建 1 行。
SQL> update t_oid set name = 'c' where id = 1;
已更新 1 行。
SQL> update t_oid set id = 5 where id = 1;
已更新 1 行。
SQL> delete t_oid;
已删除 1 行。
SQL> select * from mlog$_t_oid;
SYS_NC_OID$ SNAPTIME$$ D O CHANGE_VEC
-------------------------------- ------------------- - - ----------
94216425582C4395A987AFE6303A5CBF 4000-01-01 00:00:00 I N FE
94216425582C4395A987AFE6303A5CBF 4000-01-01 00:00:00 U U 10
94216425582C4395A987AFE6303A5CBF 4000-01-01 00:00:00 U U 08
94216425582C4395A987AFE6303A5CBF 4000-01-01 00:00:00 D O 00
SQL> select name, segcollength from sys.col$ where obj# =
2 (select object_id from user_objects where object_name = 'T_OID');
NAME SEGCOLLENGTH
-------------------- ------------
SYS_NC_OID$ 16
SYS_NC_ROWINFO$ 1
ID 22
NAME 30
NUM 22
这个结果也和ROWID类型基本一致，需要注意的是，由于对象表包含两个隐含列，因此ID不再是第一个字段，而是第三个，因此对应的值是08。
SQL> create table t (
2 col1 number,
3 col2 number,
4 col3 number,
5 col4 number,
6 col5 number,
7 col6 number,
8 col7 number,
9 col8 number,
10 col9 number,
11 col10 number,
12 col11 number,
13 col12 number
14 );
表已创建。
SQL> create materialized view log on t with rowid;
实体化视图日志已创建。
SQL> insert into t values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
已创建 1 行。
SQL> update t set col1 = 10;
已更新 1 行。
SQL> update t set col11 = 110;
已更新 1 行。
SQL> update t set col5 = 50, col12 = 120;
已更新 1 行。
SQL> delete t;
已删除 1 行。
SQL> select * from mlog$_t;
M_ROW$$ SNAPTIME$$ D O CHANGE_VEC
------------------ ------------------- - - ----------
AAACIyAAFAAAAFgAAA 4000-01-01 00:00:00 I N FEFF
AAACIyAAFAAAAFgAAA 4000-01-01 00:00:00 U U 0200
AAACIyAAFAAAAFgAAA 4000-01-01 00:00:00 U U 0008
AAACIyAAFAAAAFgAAA 4000-01-01 00:00:00 U U 2010
AAACIyAAFAAAAFgAAA 4000-01-01 00:00:00 D O 0000
最后看一个包含列数较多的例子，唯一需要注意的是，低位在左，高位在右。
转载自：http://yangtingkun.itpub.net/post/468/286875
即使将物化视图的约束建立和基表完全一致，由于物化视图的刷新机制（Oracle在刷新物化视图的时候没有保证更新的顺序），也会产生约束冲突的现象。
一个简单的例子：
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));
表已创建。
SQL> ALTER TABLE T ADD CONSTRAINT UN_T_NAME UNIQUE (NAME);
表已更改。
SQL> CREATE MATERIALIZED VIEW LOG ON T;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS SELECT * FROM T;
实体化视图已创建。
SQL> ALTER TABLE MV_T ADD CONSTRAINT UN_MV_T_NAME UNIQUE (NAME);
表已更改。
SQL> INSERT INTO T VALUES (1, 'A');
已创建 1 行。
SQL> INSERT INTO T VALUES (2, 'B');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')
PL/SQL 过程已成功完成。
上面构造了一个简单的物化视图，物化视图和基表建立了相同的唯一约束。
下面进行一个循环的更新操作，然后观察刷新的情况：
SQL> UPDATE T SET NAME = 'C' WHERE ID = 1;
已更新 1 行。
SQL> UPDATE T SET NAME = 'A' WHERE ID = 2;
已更新 1 行。
SQL> UPDATE T SET NAME = 'B' WHERE ID = 1;
已更新 1 行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')
BEGIN DBMS_MVIEW.REFRESH('MV_T'); END;
*
ERROR 位于第 1 行:
ORA-12008: 实体化视图的刷新路径中存在错误
ORA-00001: 违反唯一约束条件 (YANGTK.UN_MV_T_NAME)
ORA-06512: 在"SYS.DBMS_SNAPSHOT", line 794
ORA-06512: 在"SYS.DBMS_SNAPSHOT", line 851
ORA-06512: 在"SYS.DBMS_SNAPSHOT", line 832
ORA-06512: 在line 1
刷新失败了。解决这个问题的一个方法是对于物化视图不建立唯一约束，唯一性由基表保证。但是这种方法只对只读物化视图适用。而且，缺少唯一约束信息，可能会影响SQL的执行计划。
这种情况下，最好的解决方法是建立延迟约束。
SQL> ALTER TABLE MV_T DROP CONSTRAINT UN_MV_T_NAME;
表已更改。
SQL> ALTER TABLE MV_T ADD CONSTRAINT UN_MV_T_NAME UNIQUE (NAME) DEFERRABLE;
表已更改。
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')
PL/SQL 过程已成功完成。
注意，延迟唯一约束对应的索引也必须是非唯一的（如果是唯一索引，延迟约束将没有意义），否则无法达到延迟约束的目的。
