oracle物化視圖日志結(jié)構(gòu)是怎樣的

144次閱讀

共計 7375 個字符，預(yù)計需要花費 19 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“oracle 物化視圖日志結(jié)構(gòu)是怎樣的”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

oracle 物化視圖日志結(jié)構(gòu)

物化視圖的快速刷新要求基本必須建立物化視圖日志，這篇文章簡單描述一下物化視圖日志中各個字段的含義和用途。

物化視圖日志的名稱為 MLOG$_后面跟基表的名稱，如果表名的長度超過 20 位，則只取前 20 位，當(dāng)截短后出現(xiàn)名稱重復(fù)時，Oracle 會自動在物化視圖日志名稱后面加上數(shù)字作為序號。

物化視圖日志在建立時有多種選項：可以指定為 ROWID、PRIMARY KEY 和 OBJECT ID 幾種類型，同時還可以指定 SEQUENCE 或明確指定列名。上面這些情況產(chǎn)生的物化視圖日志的結(jié)構(gòu)都不相同。

任何物化視圖都會包括的 4 列：
SNAPTIME$$：用于表示刷新時間。
DMLTYPE$$：用于表示 DML 操作類型，I 表示 INSERT，D 表示 DELETE，U 表示 UPDATE。
OLD_NEW$$：用于表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U 表示 UPDATE 操作。
CHANGE_VECTOR$$：表示修改矢量，用來表示被修改的是哪個或哪幾個字段。
如果 WITH 后面跟了 ROWID，則物化視圖日志中會包含：M_ROW$$：用來存儲發(fā)生變化的記錄的 ROWID。
如果 WITH 后面跟了 PRIMARY KEY，則物化視圖日志中會包含主鍵列。
如果 WITH 后面跟了 OBJECT ID，則物化視圖日志中會包含：SYS_NC_OID$：用來記錄每個變化對象的對象 ID。
如果 WITH 后面跟了 SEQUENCE，則物化視圖日子中會包含：SEQUENCE$$：給每個操作一個 SEQUENCE 號，從而保證刷新時按照順序進(jìn)行刷新。
如果 WITH 后面跟了一個或多個 COLUMN 名稱，則物化視圖日志中會包含這些列。

下面通過例子進(jìn)行詳細(xì)說明：

SQL create table t_rowid (id number, name varchar2(30), num number);
表已創(chuàng)建。

SQL create materialized view log on t_rowid with rowid, sequence (name, num) including new values;
實體化視圖日志已創(chuàng)建。

SQL create table t_pk (id number primary key, name varchar2(30), num number);
表已創(chuàng)建。

SQL create materialized view log on t_pk with primary key;
實體化視圖日志已創(chuàng)建。

SQL create type t_object as object (id number, name varchar2(30), num number);
/
類型已創(chuàng)建

SQL create table t_oid of t_object;
表已創(chuàng)建。

SQL desc t_oid;
名稱是否為空? 類型
—————————————– ——– —————
ID NUMBER
NAME VARCHAR2(30)
NUM NUMBER

SQL create materialized view log on t_oid with object id;
實體化視圖日志已創(chuàng)建。

建立環(huán)境后來看看物化視圖日志中包含的字段：
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 列，因此物化視圖日志中包含了相對應(yīng)的列。

SQL desc mlog$_t_pk;
名稱是否為空? 類型
—————————————– ——– ————
ID NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

對象表的物化視圖日志建立后包含系統(tǒng)對象標(biāo)識列。
一、主鍵列、ROWID 列、OBJECT ID 列、SEQUENCE 列和建立物化視圖時指明的列。
主鍵、ROWID 或 OBJECT ID 用來唯一表示物化視圖日志中的記錄。
SEQUENCE 會根據(jù)操作發(fā)生的順序?qū)ξ锘晥D日志中的記錄編號。

建立物化視圖時指明的列會在物化視圖日志中進(jìn)行記錄。

SQL insert into t_pk values (1, a , 5);
已創(chuàng)建 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);
已創(chuàng)建 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
——————————– –
18DCFDE5D65B4D5A88602D6C09E5CE20 I
18DCFDE5D65B4D5A88602D6C09E5CE20 U
18DCFDE5D65B4D5A88602D6C09E5CE20 D

SQL rollback;

回退已完成。

二、時間列
當(dāng)基本發(fā)生 DML 操作時，會記錄到物化視圖日志中，這時指定的時間 4000 年 1 月 1 日 0 時 0 分 0 秒。如果物化視圖日志供多個物化視圖使用，則一個物化視圖刷新后會將它刷新的記錄的時間更新為它刷新的時間。
下面建立快速刷新的兩個物化視圖來演示時間列的變化。（只有建立快速刷新的物化視圖才能使用物化視圖日志，如果只建立一個物化視圖，則物化視圖刷新完會將物化視圖日志清除掉。

SQL create materialized view mv_t_rowid refresh fast on commit as select name, count(*) from t_rowid group by name;
實體化視圖已創(chuàng)建。

SQL create materialized view mv_t_rowid1 refresh fast as select name, count(*) from t_rowid group by name;
實體化視圖已創(chuàng)建。

SQL insert into t_rowid values (1, a , 5);
已創(chuàng)建 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$$
——————-
2012/5/23 15:41:41
2012/5/23 15:41:41
2012/5/23 15:41:41
2012/5/23 15:41:41
COMMIT 后，物化視圖 mv_t_rowid 刷新，將 SNAPTIME$$ 列更新成自己的刷新時間。

三、操作類型和新舊值
操作類型比較簡單：只包括 I(INSERT)、D(DELETE)和 U(UPDATE)三種。
新舊值也包括三種：O 表示舊值（一般對應(yīng)的操作時 DELETE）、N 表示新值（一般對應(yīng)的操作是 INSERT），還有一種 U（對應(yīng) UPDATE 操作）。

SQL insert into t_pk values (1, a , 5);
已創(chuàng)建 1 行。

SQL insert into t_pk values (2, b , 7);
已創(chuàng)建 1 行。

SQL insert into t_pk values (3, c , 9);
已創(chuàng)建 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 操作轉(zhuǎn)化為一條 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;
實體化視圖日志已創(chuàng)建。

SQL insert into t_rowid values (1, a , 5);
已創(chuàng)建 1 行。

SQL insert into t_rowid values (2, b , 7);
已創(chuàng)建 1 行。

SQL insert into t_rowid values (3, c , 9);
已創(chuàng)建 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 行。

查詢結(jié)果和上面類似，唯一的區(qū)別是每條 UPDATE 操作都對應(yīng)物化視圖日志中的兩條記錄。一條對應(yīng) UPDATE 操作的原記錄 DMLTYPE$$ 和 OLD_NEW$$ 都為 U，一條對應(yīng) UPDATE 操作后的新記錄，DMLTYPE$$ 為 U，OLD_NEW$$ 為 N。當(dāng)建立物化視圖日志時指出了 INCLUDING NEW VALUES 語句時，就會出現(xiàn)這種情況。

四、修改矢量
最后簡單討論一下 CHANGE_VECTOR$$ 列。
INSERT 和 DELETE 操作都是記錄集的，即 INSERT 和 DELETE 會影響整條記錄。而 UPDATE 操作是字段集的，UPDATE 操作可能會更新整條記錄的所有字段，也可能只更新個別字段。
無論從性能上考慮還是從數(shù)據(jù)的一致性上考慮，物化視圖刷新時都應(yīng)該是基于字段集。Oracle 就是通過 CHANGE_VECTOR$$ 列來記錄每條記錄發(fā)生變化的字段包括哪些。

基于主鍵、ROWID 和 OBJECT ID 的物化視圖日志在 CHANGE_VECTOR$$ 上略有不同，但是總體設(shè)計的思路是一致的。

CHANGE_VECTOR$$ 列是 RAW 類型，其實 Oracle 采用的方式就是用每個 BIT 位去映射一個列。

比如：第一列被更新設(shè)置為 02，即 00000010。第二列設(shè)置為 04，即 00000100，第三列設(shè)置為 08，即 00001000。當(dāng)?shù)谝涣泻偷诙型瑫r被更新，則設(shè)置為 06，00000110。如果三列都被更新，設(shè)置為 0E，00001110。
依此類推，第 4 列被更新時為 10，第 5 列 20，第 6 列 40，第 7 列 80，第 8 列 0001。當(dāng)?shù)?1000 列被更新時，CHANGE_VECTOR$$ 的長度為 1000/4+ 2 為 252。

除了可以表示 UPDATE 的字段，還可以表示 INSERT 和 DELETE。DELETE 操作 CHANGE_VECTOR$$ 列為全 0，具體個數(shù)由基表的列數(shù)決定。INSERT 操作的最低位為 FE 如果基表列數(shù)較多，而存在高位的話，所有的高位都為 FF。如果 INSERT 操作是前面討論過的由 UPDATE 操作更新了主鍵造成的，則這個 INSERT 操作對應(yīng)的 CHANGE_VECTOR$$ 列為全 FF。

SQL insert into t_rowid values (1, a , 5);
已創(chuàng)建 1 行。

SQL insert into t_rowid values (2, b , 7);
已創(chuàng)建 1 行。

SQL insert into t_rowid values (3, c , 9);
已創(chuàng)建 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;

可以看到，正如上面分析的，INSERT 為 FE，DELETE 為 00，對第一列的更新為 02，第二列為 04，第二列和第三列都更新為 0C。需要注意，正常情況下，第一列會從 02 開始，但是如果對 MLOG$ 表執(zhí)行了 TRUNCATE 操作，或者重建了物化視圖日志，則可能造成第一列開始位置發(fā)生偏移。

SQL insert into t_pk values (1, a , 5);
已創(chuàng)建 1 行。

SQL insert into t_pk values (2, b , 7);
已創(chuàng)建 1 行。

SQL insert into t_pk values (3, c , 9);
已創(chuàng)建 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 commit
提交完成。

SQL select * from mlog$_t_pk;

這個結(jié)果和 ROWID 類型基本一致，不同的是，如果更新了主鍵，會將 UPDATE 操作在物化視圖日志中記錄為一條 DELETE 和一條 INSERT，不過這時 INSERT 對應(yīng)的 CHANGE_VECTOR$$ 的值是 FF。

SQL insert into t_oid values (1, a , 5);
已創(chuàng)建 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 commit;
提交完成。

SQL select * from mlog$_t_oid;

SQL 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

這個結(jié)果也和 ROWID 類型基本一致，需要注意的是，由于對象表包含兩個隱含列，因此 ID 不再是第一個字段，而是第三個，因此對應(yīng)的值是 08。

SQL 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
);
表已創(chuàng)建。

SQL create materialized view log on t with rowid;
實體化視圖日志已創(chuàng)建。

SQL insert into t values (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
已創(chuàng)建 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 commit;
提交完成。

SQL select * from mlog$_t;