如何根據MLOG$表的CHANGE_VECTOR$$找出被更新的列

在yangtingkun老大的指點下，對主鍵重新整理物化檢視日誌表的CHANGE_VECTOR$$有了進一步的認識，下面就對這個新的知識點做一個簡單的總結。

我們知道，oracle用MLOG$_XXX表來記錄發生在基表上的資料變化，以此作為物化檢視重新整理的依據。
先看看MLOG$的表結構:
SQL> DESC MLOG$_TEST
Name Type Nullable Default Comments
--------------- ----------- -------- ------- --------
C1 NUMBER Y
SNAPTIME$$ DATE Y
DMLTYPE$$ VARCHAR2(1) Y
OLD_NEW$$ VARCHAR2(1) Y
CHANGE_VECTOR$$ RAW(255) Y

其中：CHANGE_VECTOR$$記錄的是記錄的改變向量。
一般來說，INSERT 和 DELETE操作對應的改變向量是固定的。如：

SQL> INSERT INTO TEST VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

1 row inserted

SQL> DELETE FROM TEST;

1 row deleted

SQL> SELECT * FROM MLOG$_TEST;

C1 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
---------- ----------- --------- --------- ------------------------
1 4000-1-1 I N FEFFFFFF
1 4000-1-1 D O 00000000

新增對應的CHANGE_VECTOR$$為：FEFFFFFF
刪除對應的CHANGE_VECTOR$$為：00000000

這兩種操作較為簡單，這裡不討論。對於UPDATE操作的
先引用一段yangtingkun blog上的話：
CHANGE_VECTOR$$列是RAW型別，其實Oracle採用的方式就是用每個BIT位去對映一個列。
比如：第一列被更新設定為02，即00000010。第二列設定為04，即00000100，第三列設定為08，即00001000。
當第一列和第二列同時被更新，則設定為06，00000110。如果三列都被更新，設定為0E，00001110。

如果修改的列數很多，需要多位來表示CHANGE_VECTOR$$的話，注意：低位在左，高位在右。
如：24109444轉換稱由高向低的順序應該是：44941024

概括來說就是：
CHANGE_VECTOR$$每兩個數字或者字母為一個單元，這些單元之間按低位在左，高位在右的規則存放。

CHANGE_VECTOR$$都可以的計算公式是：(2的n1次方 + 2的n2次方 + 2的n3次方 .....)
其中n1、n2、n3等對應的就是被更新列的column_id。

知道了演算法，要透過CHANGE_VECTOR$$得到被更新的是那些列就簡單了。我這裡透過一個函式和一個過程實現了。

--------------將改變向量轉換為從高位到低位的順序----------------
create or replace function f_CHANGE_VECTOR(p_CHANGE_VECTOR varchar2) return varchar2 is
CHANGE_VECTOR varchar2(255);
-- 將改變向量轉換為從高位到低位的順序
begin
select replace(max(substr(sys_connect_by_path(cv, ','), 2)), ',', '') into CHANGE_VECTOR
from (select cv, rownum rn
from (select substr(p_CHANGE_VECTOR, rn * 2 + 1, 2) cv, rownum rn
from dual,
(select rownum - 1 rn
from all_objects
where rownum <= length(p_CHANGE_VECTOR) / 2) r
order by 2 desc))
start with rn = 1
connect by rn = rownum;
return(CHANGE_VECTOR);
end ;

----------------根據CHANGE_VECTOR$$得到被更新列的列表---------------

create or replace procedure p_get_column(p_bin number, p_table varchar2) is
l_column_id number;
l_log number;
l_bin number;
l_power number;
l_temp_bin number;
l_COLUMN_NAME varchar2(4000);
begin

if p_bin = 0 then
--只更新主鍵，且鍵值不變,則主鍵部分對應的向量值為0
select max(substr(sys_connect_by_path(column_name, ','), 2))
into l_COLUMN_NAME
from (select column_name, rownum rn
from (select column_name
from user_constraints uc, user_cons_columns uuc
where uc.table_name = upper(p_table)
and uc.CONSTRAINT_NAME = uuc.CONSTRAINT_NAME
order by position))
start with rn = 1
connect by rn = rownum;
dbms_output.put_line(p_table || '(' || l_COLUMN_NAME || ')');

else
/*
--分兩種情況：
1、更新列包含主鍵，但列值不變
2、更新列不一定包含主鍵(如果包含的話，鍵值不變)
鍵值前後不變的情況下，則主鍵部分對應的向量值為0，n+0=n，所以不能確定更新列是否包含主鍵

由於主鍵鍵值改變的情況下，MLOG$會拆分成D和I兩個操作，所以這裡不需要討論這種情況
*/
--log函式有精度損失，所以需要用round來進行四捨五入，6為小數可以保證精度在一個可接受範圍內
select round(log(2, p_bin), 6) into l_log from dual;
l_column_id := floor(l_log);
select column_name
into l_COLUMN_NAME
from user_tab_columns
where table_name = upper(p_table)
and column_id = l_column_id;
dbms_output.put_line(p_table || '(' || l_COLUMN_NAME || ')');
l_power := round(l_log);
--由於前面的log函式和round的函式都造成資料精度損失，所以需要重新用power糾正資料
l_temp_bin := power(2, l_power);
if p_bin <> l_temp_bin then

--透過遞迴求出所有的指數
l_bin := p_bin - power(2, l_power);
p_get_column(l_bin, p_table);
end if;
end if;
end p_get_column;



-----------下面用資料來檢驗----------------------

SQL> update test set c20=20,c23=23,c12=12;

1 row updated

SQL> update test set c10=10;

1 row updated

SQL> update test set c20=20,c23=23,c12=12,c30=30,c2=2,c5=5,c18=18,c26=26;

1 row updated

SQL> commit;

Commit complete

SQL> select * from mlog$_test;

C1 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
---------- ----------- --------- --------- -----------------------------
1 4000-1-1 U U 00109000
1 4000-1-1 U U 00040000
1 4000-1-1 U U 24109444


SQL> EXEC p_get_column(TO_NUMBER(f_change_vector('00109000'),'XXXXXXXX'),'TEST')

TEST(C23)
TEST(C20)
TEST(C12)

PL/SQL procedure successfully completed

SQL> EXEC p_get_column(TO_NUMBER(f_change_vector('00040000'),'XXXXXXXX'),'TEST')

TEST(C10)

PL/SQL procedure successfully completed

SQL> EXEC p_get_column(TO_NUMBER(f_change_vector('24109444'),'XXXXXXXX'),'TEST')

TEST(C30)
TEST(C26)
TEST(C23)
TEST(C20)
TEST(C18)
TEST(C12)
TEST(C5)
TEST(C2)

PL/SQL procedure successfully completed

從以上測試可以看到，演算法是正確的。