【MV】物化视图测试

  物化视图作为Oracle性能调优的一种有效手段值得深入测试和了解，在充分了解和测试物化视图技术的基础上，可以将物化视图技术纳入到数据库架构设计过程中。本文对物化视图的创建以及物化视图的基本知识点进行描述。

1.创建测试环境

create tablespace sectbs datafile

'E:\oracle\product\10.1.0\oradata\orcl\sectbs.dbf' size 10m

autoextend on

extent management local

segment space management auto

/

create user sec

identified by sec

default tablespace sectbs

temporary tablespace temp

/

grant create session,resource,create table,dba to sec

/

create table t_rowid (id number, name varchar2(30), num number);

表已创建。

create materialized view log on t_rowid with rowid, sequence (name, num) including new values;

实体化视图日志已创建。

create table t_pk (id number primary key, name varchar2(30), num number);

表已创建。

create materialized view log on t_pk with primary key;

实体化视图日志已创建。

create type t_object as object (id number, name varchar2(30), num number)

/

类型已创建。

create table t_oid of t_object;

表已创建。

create materialized view log on t_oid with object id;

实体化视图日志已创建。

select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

T_ROWID                        TABLE

MLOG$_T_ROWID                  TABLE

T_PK                           TABLE

MLOG$_T_PK                     TABLE

RUPD$_T_PK                     TABLE

T_OID                          TABLE

MLOG$_T_OID                    TABLE

已选择7行。

2.物化视图包括的列描述

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名称，则物化视图日志中会包含这些列。

3.主键列、ROWID列、OBJECT ID列、SEQUENCE列和建立物化视图时指明的列

主键、ROWID或OBJECT ID用来唯一表示物化视图日志中的记录。

SEQUENCE会根据操作发生的顺序对物化视图日志中的记录编号。

建立物化视图时指明的列会在物化视图日志中进行记录。

col M_ROW$$ for a30

col name for a10

col CHANGE_VECTOR$$ for a10

insert into t_rowid values (1, 'a', 5);

已创建 1 行。

update t_rowid set name = 'c' where id = 1;

已更新 1 行。

delete t_rowid;

已删除 1 行。

col M_ROW$$ for a30

col name for a10

col CHANGE_VECTOR$$ for a10

select * from mlog$_t_rowid;

NAME NUM M_ROW$$            SEQUENCE$$ SNAPTIME$$ D O CHANGE_VEC

---- --- ------------------ ---------- ---------- - - ----------

a      5 AAAMWQAAGAAAAAOAAA          1 01-1月 -00 I N FE

a      5 AAAMWQAAGAAAAAOAAA          2 01-1月 -00 U U 04

c      5 AAAMWQAAGAAAAAOAAA          3 01-1月 -00 U N 04

c      5 AAAMWQAAGAAAAAOAAA          4 01-1月 -00 D O 00

insert into t_pk values (1, 'a', 5);

已创建 1 行。

update t_pk set name = 'c' where id = 1;

已更新 1 行。

delete t_pk;

已删除 1 行。

select * from mlog$_t_pk;

        ID SNAPTIME$$     D O CHANGE_VEC

---------- -------------- - - ----------

         1 01-1月 -00     I N FE

         1 01-1月 -00     U U 04

         1 01-1月 -00     D O 00

insert into t_oid values (1, 'a', 5);

已创建 1 行。

update t_oid set name = 'c' where id = 1;

已更新 1 行。

delete t_oid;

已删除 1 行。

select * from mlog$_t_oid;

SYS_NC_OID$                      SNAPTIME$$     D O CHANGE_VEC

-------------------------------- -------------- - - ----------

A745AE7580624F71AFAC1F1A81D74E0D 01-1月 -00     I N FE

A745AE7580624F71AFAC1F1A81D74E0D 01-1月 -00     U U 10

A745AE7580624F71AFAC1F1A81D74E0D 01-1月 -00     D O 00

rollback;

回退已完成。

4.时间列

当基表发生DML操作时，会记录到物化视图日志中，这时指定的时间4000年1月1日0时0分0秒。如果物化视图日志供多个物化视图使用，

则一个物化视图刷新后会将它刷新的记录的时间更新为它刷新的时间。

下面建立快速刷新的两个物化视图来演示时间列的变化。

（只有建立快速刷新的物化视图才能使用物化视图日志，如果只建立一个物化视图，则物化视图刷新完会将物化视图日志清除掉。

create materialized view mv_t_rowid refresh fast on commit as

  select name, count(*) from t_rowid group by name;

实体化视图已创建。

create materialized view mv_t_rowid1 refresh fast as

  select name, count(*) from t_rowid group by name;

实体化视图已创建。

insert into t_rowid values (1, 'a', 5);

已创建 1 行。

update t_rowid set name = 'c' where id = 1;

已更新 1 行。

delete t_rowid;

已删除 1 行。

select select NAME,NUM,M_ROW$$,SEQUENCE$$,to_char(SNAPTIME$$,'YYYY-MM-DD HH24:MI:SS') SNAPTIME$$,DMLTYPE$$,OLD_NEW$$,CHANGE_VECTOR$$ from mlog$_t_rowid;

NAME NUM M_ROW$$            SEQUENCE$$ SNAPTIME$$          D O CHANGE_VEC

---- --- ------------------ ---------- ------------------- - - ----------

a      5 AAAMWQAAGAAAAAOAAA          5 4000-01-01 00:00:00 I N FE

a      5 AAAMWQAAGAAAAAOAAA          6 4000-01-01 00:00:00 U U 04

c      5 AAAMWQAAGAAAAAOAAA          7 4000-01-01 00:00:00 U N 04

c      5 AAAMWQAAGAAAAAOAAA          8 4000-01-01 00:00:00 D O 00

commit;

提交完成。

select select NAME,NUM,M_ROW$$,SEQUENCE$$,to_char(SNAPTIME$$,'YYYY-MM-DD HH24:MI:SS') SNAPTIME$$,DMLTYPE$$,OLD_NEW$$,CHANGE_VECTOR$$ 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$$列更新成自己的刷新时间。

5.操作类型和新旧值

操作类型比较简单：只包括I(INSERT)、D(DELETE)和U(UPDATE)三种。

新旧值也包括三种：O表示旧值（一般对应的操作时DELETE）、N表示新值（一般对应的操作是INSERT），还有一种U（对应UPDATE操作）。

insert into t_pk values (1, 'a', 5);

已创建 1 行。

insert into t_pk values (2, 'b', 7);

已创建 1 行。

insert into t_pk values (3, 'c', 9);

已创建 1 行。

update t_pk set name = 'c' where id = 1;

已更新 1 行。

update t_pk set id = 4 where id = 2;

已更新 1 行。

delete t_pk where id = 3;

已删除 1 行。

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操作。

drop materialized view log on t_rowid;

实体化视图日志已删除。

create materialized view log on t_rowid with rowid, sequence (name, num) including new values;

实体化视图日志已创建。

insert into t_rowid values (1, 'a', 5);

已创建 1 行。

insert into t_rowid values (2, 'b', 7);

已创建 1 行。

insert into t_rowid values (3, 'c', 9);

已创建 1 行。

update t_rowid set name = 'c' where id = 1;

已更新 1 行。

update t_rowid set id = 4 where id = 2;

已更新 1 行。

delete t_rowid where id = 3;

已删除 1 行。

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语句时，就会出现这种情况。

6.修改矢量

最后简单讨论一下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。

insert into t_rowid values (1, 'a', 5);

已创建 1 行。

insert into t_rowid values (2, 'b', 7);

已创建 1 行。

insert into t_rowid values (3, 'c', 9);

已创建 1 行。

update t_rowid set name = 'c' where id = 1;

已更新 1 行。

update t_rowid set id = 4 where id = 2;

已更新 1 行。

update t_rowid set name = 'd', num = 11 where id = 3;

已更新 1 行。

delete t_rowid where id = 3;

已删除 1 行。

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操作，或者重建了物化视图日志，则可能造成第一列开始位置发生偏移。

insert into t_pk values (1, 'a', 5);

已创建 1 行。

insert into t_pk values (2, 'b', 7);

已创建 1 行。

insert into t_pk values (3, 'c', 9);

已创建 1 行。

update t_pk set name = 'c' where id = 1;

已更新 1 行。

update t_pk set id = 4 where id = 2;

已更新 1 行。

delete t_pk where id = 1;

已删除 1 行。

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。

insert into t_oid values (1, 'a', 5);

已创建 1 行。

update t_oid set name = 'c' where id = 1;

已更新 1 行。

update t_oid set id = 5 where id = 1;

已更新 1 行。

delete t_oid;

已删除 1 行。

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

select name, segcollength from sys.col$ where obj# =

  (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。

create table t (

  col1 number,

  col2 number,

  col3 number,

  col4 number,

  col5 number,

  col6 number,

  col7 number,

  col8 number,

  col9 number,

  col10 number,

  col11 number,

  col12 number

  );

表已创建。

create materialized view log on t with rowid;

实体化视图日志已创建。

insert into t values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

已创建 1 行。

update t set col1 = 10;

已更新 1 行。

update t set col11 = 110;

已更新 1 行。

update t set col5 = 50, col12 = 120;

已更新 1 行。

delete t;

已删除 1 行。

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

最后看一个包含列数较多的例子，唯一需要注意的是，低位在左，高位在右。

7.小结

  本文对物化视图的基本操作做了一个完整的演示。物化视图在数据仓库类型系统中使用较多。是一种典型的空间换时间的技术。

Good luck.

secooler

11.05.31

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-563084/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-563084/