做淘宝客网站能有效果吗,青岛网站建设推进,wordpress3.9漏洞,月付购物网站建站当表建立了物化视图日志之后#xff0c;表的DML修改会被记录到物化视图日志中#xff0c;而物化视图日志则包含了一个修改矢量#xff0c;来记录哪个列被修改。在文章列的修改矢量可以通过2的N次方来获得#xff0c;也就是POWER(2, N)。而N的值#xff0c;就是列的位置。但…当表建立了物化视图日志之后表的DML修改会被记录到物化视图日志中而物化视图日志则包含了一个修改矢量来记录哪个列被修改。在文章列的修改矢量可以通过2的N次方来获得也就是POWER(2, N)。而N的值就是列的位置。但是如果存在一些隐藏的列通过DBA_TAB_COLUMNS来获取列的位置就存在问题比如SQL CREATE TABLE T_PERSON OF T_PERSON_TYP2 (ID PRIMARY KEY);Table created.SQL SELECT COLUMN_ID2 FROM DBA_TAB_COLUMNS3 WHERE WNER USER4 AND TABLE_NAME T_PERSON5 AND COLUMN_NAME AGE;COLUMN_ID----------3SQL SELECT INTCOL#2 FROM SYS.COL$3 WHERE NAME AGE4 AND OBJ# 5 (SELECT OBJECT_ID6 FROM DBA_OBJECTS7 WHERE WNER USER8 AND OBJECT_NAME T_PERSON9 AND OBJECT_TYPE TABLE);INTCOL#----------5SQL CREATE MATERIALIZED VIEW LOG ON T_PERSON;Materialized view log created.SQL INSERT INTO T_PERSON VALUES (1, ABC, 18, BCD);1 row created.SQL UPDATE T_PERSON SET AGE 20 WHERE ID 1;1 row updated.SQL COL CHANGE_VECTOR$$ FORMAT A30SQL SELECT * FROM MLOG$_T_PERSON;ID SNAPTIME$$ D O CHANGE_VECTOR$$---------- -------------- - - ------------------------------1 01-1月-00 I N FE1 01-1月-00 U U 20SQL SELECT TO_CHAR(POWER(2, 5), XX) FROM DUAL;TO_---20SQL SELECT TO_CHAR(POWER(2, 3), XX) FROM DUAL;TO_---8很显然物化视图日志中获取的列的偏移量是通过SYS.COL$视图的INTCOL#列获取的而不是通过DBA_TAB_COLUMNS视图。表SYS.COL$中INTCOL#的值就是我们要计算的N得到POWER(2, N)后通过TO_CHAR转换为16进制的RAW就是这个列对应的偏移量。不过还需要注意一点CHANGE_VECTOR$$中的偏移量和直接TO_CHAR得到的结果还是有区别的首先二者的排列顺序就有区别SQL DROP TABLE T_PERSON PURGE;Table dropped.SQL DROP TYPE T_PERSON_TYP;Type dropped.SQL CREATE TABLE T_PERSON2 (ID NUMBER PRIMARY KEY,3 NAME VARCHAR2(30),4 ADDR01 VARCHAR2(30),5 ADDR02 VARCHAR2(30),6 ADDR03 VARCHAR2(30),7 ADDR04 VARCHAR2(30),8 ADDR05 VARCHAR2(30),9 ADDR06 VARCHAR2(30),10 ADDR07 VARCHAR2(30),11 ADDR08 VARCHAR2(30),12 ADDR09 VARCHAR2(30),13 ADDR10 VARCHAR2(30),14 ADDR11 VARCHAR2(30),15 ADDR12 VARCHAR2(30),16 ADDR13 VARCHAR2(30),17 ADDR14 VARCHAR2(30),18 ADDR15 VARCHAR2(30),19 ADDR16 VARCHAR2(30),20 ADDR17 VARCHAR2(30),21 ADDR18 VARCHAR2(30),22 AGE NUMBER);Table created.SQL CREATE MATERIALIZED VIEW LOG ON T_PERSON;Materialized view log created.SQL SELECT TO_CHAR(POWER(2, INTCOL#), XXXXXX)2 FROM SYS.COL$3 WHERE NAME AGE4 AND OBJ# 5 (SELECT OBJECT_ID6 FROM DBA_OBJECTS7 WHERE WNER USER8 AND OBJECT_NAME T_PERSON9 AND OBJECT_TYPE TABLE);TO_CHAR-------200000SQL INSERT INTO T_PERSON2 (ID, NAME, AGE)3 VALUES (1, A, 15);1 row created.SQL UPDATE T_PERSON SET AGE 18;1 row updated.SQL SELECT * FROM MLOG$_T_PERSON;ID SNAPTIME$$ D O CHANGE_VECTOR$$---------- -------------- - - ------------------------------1 01-1月-00 I N FEFFFF1 01-1月-00 U U 000020显然物化视图日志中的偏移量是逆向的好在Oracle的内置函数REVERSE也支持RAW类型省得自己编写这个函数了SQL SELECT REVERSE(LTRIM(TO_CHAR(POWER(2, INTCOL#), XXXXXX)))2 FROM SYS.COL$3 WHERE NAME AGE4 AND OBJ# 5 (SELECT OBJECT_ID6 FROM DBA_OBJECTS7 WHERE WNER USER8 AND OBJECT_NAME T_PERSON9 AND OBJECT_TYPE TABLE);REVERSE-------000002这里还有问题REVERSE采用字符串的反转方法而这时需要RAW类型的反转SQL SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), XXXXXX)) AS RAW(255)))2 FROM SYS.COL$3 WHERE NAME AGE4 AND OBJ# 5 (SELECT OBJECT_ID6 FROM DBA_OBJECTS7 WHERE WNER USER8 AND OBJECT_NAME T_PERSON9 AND OBJECT_TYPE TABLE);REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),XXXXXX))ASRAW(255)))------------------------------------------------------------------000020下面还有一些问题首先就是TO_CHAR后结果的前缀0问题SQL UPDATE T_PERSON SET ADDR08 5;1 row updated.SQL SELECT * FROM MLOG$_T_PERSON;ID SNAPTIME$$ D O CHANGE_VECTOR$$---------- -------------- - - ------------------------------1 01-1月-00 I N FEFFFF1 01-1月-00 U U 0000201 01-1月-00 U U 000400SQL SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), XXXXXX)) AS RAW(255)))2 FROM SYS.COL$3 WHERE NAME ADDR084 AND OBJ# 5 (SELECT OBJECT_ID6 FROM DBA_OBJECTS7 WHERE WNER USER8 AND OBJECT_NAME T_PERSON9 AND OBJECT_TYPE TABLE);REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),XXXXXX))ASRAW(255)))------------------------------------------------------------------0004在TO_CHAR的时候使用’0X’作为参数可以避免前缀0的问题SQL SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), 0XXXXX)) AS RAW(255)))2 FROM SYS.COL$3 WHERE NAME ADDR084 AND OBJ# 5 (SELECT OBJECT_ID6 FROM DBA_OBJECTS7 WHERE WNER USER8 AND OBJECT_NAME T_PERSON9 AND OBJECT_TYPE TABLE);REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),0XXXXX))ASRAW(255)))------------------------------------------------------------------000400但是另一个麻烦的问题又出现了就是执行TO_CHAR的时候需要指定多少个XX的个数如果少了会报错个数多了结果又不正确。其中X的个数由表中总的列数来决定具体算法为SQL SELECT FLOOR(LOG(256, POWER(2, COUNT(*)))) 12 FROM SYS.COL$3 WHERE OBJ# 4 (SELECT OBJECT_ID5 FROM DBA_OBJECTS6 WHERE WNER USER7 AND OBJECT_NAME T_PERSON8 AND OBJECT_TYPE TABLE);FLOOR(LOG(256,POWER(2,COUNT(*))))1-----------------------------------3将上面的结果整合在一起SQL SELECT REVERSE(CAST(LTRIM(TO_CHAR(NUM, RPAD(0X, CNT * 2, X))) AS RAW(255)))2 FROM3 (4 SELECT POWER(2, INTCOL#) NUM,5 FLOOR(LOG(256, POWER(2, COUNT(*) OVER()))) 1 CNT,6 NAME7 FROM SYS.COL$8 WHERE OBJ# 9 (10 SELECT OBJECT_ID11 FROM DBA_OBJECTS12 WHERE WNER USER13 AND OBJECT_NAME T_PERSON14 AND OBJECT_TYPE TABLE15 )16 )17 WHERE NAME ADDR13;REVERSE(CAST(LTRIM(TO_CHAR(NUM,RPAD(0X,CNT*2,X)))ASRAW(255)))-----------------------------------------------------------------008000SQL SELECT REVERSE(CAST(LTRIM(TO_CHAR(NUM, RPAD(0X, CNT * 2, X))) AS RAW(255)))2 FROM3 (4 SELECT POWER(2, INTCOL#) NUM,5 FLOOR(LOG(256, POWER(2, COUNT(*) OVER()))) 1 CNT,6 NAME7 FROM SYS.COL$8 WHERE OBJ# 9 (10 SELECT OBJECT_ID11 FROM DBA_OBJECTS12 WHERE WNER USER13 AND OBJECT_NAME T_PERSON14 AND OBJECT_TYPE TABLE15 )16 )17 WHERE NAME AGE;REVERSE(CAST(LTRIM(TO_CHAR(NUM,RPAD(0X,CNT*2,X)))ASRAW(255)))-----------------------------------------------------------------000020
