如何根据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

从以上测试可以看到，算法是正确的。

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

转载于:http://blog.itpub.net/231499/viewspace-63803/