【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/