Oracle補全日誌(Supplemental logging)

Oracle補全日誌(Supplemental logging)特性因其作用的不同可分為以下幾種：最小(Minimal),支持所有欄位(all),支持主鍵(primary key),支持唯一鍵(unique),支持外鍵(foreign key)。包括LONG,LOB,LONG RAW及集合等欄位類型均無法利用補全日誌。

最小(Minimal)補全日誌開啟後可以使得logmnr工具支持鏈式行，簇表和索引組織表。可以通過以下SQL檢查最小補全日誌是否已經開啟：
SELECT supplemental_log_data_min FROM v$database;

若結果返回YES或IMPLICIT則說明已開啟最小補全日誌，當使用ALL,PRIMARY,UNIQUE或FOREIGN補全日誌時最小補全日誌預設開啟(即檢查結果為IMPLICIT)。

一般情況下我們在使用邏輯備庫時啟用主鍵和惟一鍵的補全日誌，而有時表上可能沒有主鍵，惟一鍵或唯一索引；我們通過以下實驗總結這種情況下Oracle的表現。

首先建立相關的測試表：
alter database add supplemental log data (primary key,unique index) columns ;

create table test (t1 int , t2 int ,t3 int ,t4 int )；

alter table test add constraint pk_t1 primary key (t1); –添加主鍵

隨後使用迴圈插入一定量的數據

update test set t2=10;       commit;   — 更新數據

使用LOGMNR工具分析之前的操作，可以看到REDO中記錄的SQL形式如下：
update “SYS”.”TEST” set “T2” = ’10’ where “T1” = ’64’ and “T2” = ’65’ and ROWID = ‘AAAMiSAABAAAOhiAA/’;

其中where字句後分別記錄了主鍵值，被修改欄位的值和原行的ROWID。

現在我們將原表上的主鍵去掉來觀察。
alter table test drop constraint pk_t1 ;

update test set t2=11;       commit;   — 更新數據

使用LOGMNR分析可以發現，REDO中的SQL記錄如下：

update “SYS”.”TEST” set “T2” = ’11’ where “T1” = ‘1’ and “T2” = ’10’ and “T3” = ‘3’ and “T4” = ‘4’ and ROWID = ‘AAAMiSAABAAAOhiAAA’;

當沒有主鍵的情況下，where子句後記錄了所有列值和ROWID。
以下實驗在存在唯一索引情況下的表現

create unique index pk_t1 on test(t1);

update test set t2=15; commit;

使用LOGMNR分析可以發現，REDO中的SQL記錄如下：

update “SYS”.”TEST” set “T2” = ’15’ where “T1” = ‘9’ and “T2” = ’11’ and “T3” = ’11’ and “T4” = ’12’ and ROWID = ‘AAAMiSAABAAAOhiAAI’;

以上是t1列有唯一索引但不限定not null的情況，下麵我們加上not null限制

alter table test modify t1 not null；

update test set t2=21; commit;

使用LOGMNR分析可以發現，REDO中的SQL記錄如下：

update “SYS”.”TEST” set “T2” = ’21’ where “T1” = ‘2’ and “T2” = ’15’ and ROWID = ‘AAAMiSAABAAAOhiAAB’;

如以上SQL所示，在存在唯一索引的情況下where子句後仍記錄了所有列和ROWID;在存在唯一索引和非空約束的情況下表現與存在主鍵的情況一致。

當某個表上的列數量較多時且沒有主鍵或唯一索引和非空約束的情況下，開啟補全日誌可能導致重做日誌總量大幅提高。
首先建立一個存在250列的表：

Drop table test;

create table test (

t1 varchar2(5),

t2 varchar2(5),

t3 varchar2(5),

t4 varchar2(5),  …t250 varchar2(5))

insert into test values (‘TEST’,’TEST’ ……);   commit; –將255個列填入數據

alter database drop supplemental log data (primary key,unique index) columns;  –關閉補全日誌

set autotrace on;

update test set t2=’BZZZZ’ where t1=’TEST’; commit;

可以從自動跟蹤信息中看到，本條更新產生了516的重做量。

alter database add supplemental log data (primary key,unique index) columns；  –重新開啟補全日誌

update test set t2=’FSDSD’ where t1=’TEST’;

跟蹤信息顯示產生了3044的重做量。

補全日誌因作用域的不同又可分為資料庫級的和表級的。表級補全日誌又可以分為有條件的和無條件的。有條件限制的表級補全日誌僅在特定列被更新時才會起作用，有條件限制的表級補全日誌較少使用，這裡我們不做討論。

下麵我們來觀察無條件限製表級補全日誌的具體表現：
alter database drop supplemental log data (primary key,unique index) columns;

alter table test add supplemental log data (primary key,unique index) columns;

update test set t2=’ZZZZZ’; commit;

使用LOGMNR工具查看redo中的SQL:
update “SYS”.”TEST” set “T2” = ‘ZZZZZ’ where “T1” = ‘TEST’ and “T2” = ‘AAAAA’ and “T3” = ‘TEST’………

可以發現where子句之後包含了所有列值。

delete test; commit;

使用LOGMNR工具查看redo中的SQL:

delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘ZZZZZ’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” ……

delete操作同樣在where子句之後包含了所有列值。

又我們可以針對錶上欄位建立特定的補全日誌組，以減少where子句後列值的出現。

alter table test drop supplemental log data (primary key,unique index) columns;  –關閉表上原先的補全日誌

alter table test add supplemental log group test_lgp (t1 ,t2,t3,t4,t5,t6,t12,t250) always; –創建補全日誌組

update test set t2=’XXXXX’ ; commit;

使用LOGMNR工具查看redo中的SQL:

update “SYS”.”TEST” set “T2” = ‘XXXXX’ where “T1” = ‘TEST’ and “T2” = ‘TEST’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” = ‘TEST’ and “T6” = ‘TEST’ and “T12” = ‘TEST’ and “T250” = ‘TEST’ and ROWID = ‘AAAMieAABAAAOhnAAA’;

如上所示重做日誌中正確地顯示了UPDATE操作中用戶指定的欄位值。

delete test;

使用LOGMNR工具查看redo中的SQL:

delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘XXXXX’ and “T3” = ‘TEST’ ……

delete操作在重做日誌中仍然保留了所有列值。

針對欄位較多的表，我們在能夠以多個列保證數據唯一性且非空的情況下(即應用概念上的主鍵)來指定表上的補全日誌組，以減少update操作時所產生的重做日誌，而對於delete操作則無法有效改善。