1. 首页
  2. IT资讯

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

在yangtingkun老大的指点下,对主键刷新物化视图日志表的CHANGE_VECTOR$$有了进一步的认识,下面就对这个新的知识点做一个简单的总结。

我们知道,oracle用MLOG$_XXX表来记录发生在基表上的数据变化,以此作为物化视图刷新的依据。先看看MLOG$的表结构:SQL> DESC MLOG$_TESTName 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 insertedSQL> DELETE FROM TEST;1 row deletedSQL> 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 = 1connect 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 updatedSQL> update test set c10=10;1 row updatedSQL> update test set c20=20,c23=23,c12=12,c30=30,c2=2,c5=5,c18=18,c26=26;1 row updatedSQL> commit;Commit completeSQL> 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 24109444SQL> EXEC p_get_column(TO_NUMBER(f_change_vector(‘00109000′),’XXXXXXXX’),’TEST’)TEST(C23)TEST(C20)TEST(C12)PL/SQL procedure successfully completedSQL> EXEC p_get_column(TO_NUMBER(f_change_vector(‘00040000′),’XXXXXXXX’),’TEST’)TEST(C10)PL/SQL procedure successfully completedSQL> 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://www.cxybcw.com/195603.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code