oracle修改物化视图字段,获取物化视图字段的修改矢量（一）

当表建立了物化视图日志之后，表的DML修改会被记录到物化视图日志中，而物化视图日志则包含了一个修改矢量，来记录哪个列被修改。

在文章

列的修改矢量可以通过2的N次方来获得，也就是POWER(2, N)。而N的值，就是列的位置。但是如果存在一些隐藏的列，通过DBA_TAB_COLUMNS来获取列的位置就存在问题，比如：

SQL> CREATE TABLE T_PERSON OF T_PERSON_TYP

2  (ID PRIMARY KEY);

Table created.

SQL> SELECT COLUMN_ID

2  FROM DBA_TAB_COLUMNS

3  WHERE WNER = USER

4  AND TABLE_NAME = 'T_PERSON'

5  AND COLUMN_NAME = 'AGE';

COLUMN_ID

----------

3

SQL> SELECT INTCOL#

2  FROM SYS.COL$

3  WHERE NAME = 'AGE'

4  AND OBJ# =

5  (SELECT OBJECT_ID

6  FROM DBA_OBJECTS

7  WHERE WNER = USER

8  AND OBJECT_NAME = 'T_PERSON'

9  AND OBJECT_TYPE = 'TABLE');

INTCOL#

----------

5

SQL> 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 A30

SQL> SELECT * FROM MLOG$_T_PERSON;

ID SNAPTIME$$     D O CHANGE_VECTOR$$

---------- -------------- - - ------------------------------

1 01-1月-00     I N FE

1 01-1月-00     U U 20

SQL> SELECT TO_CHAR(POWER(2, 5), 'XX') FROM DUAL;

TO_

---

20

SQL> 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_PERSON

2  (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 = 'AGE'

4  AND OBJ# =

5  (SELECT OBJECT_ID

6  FROM DBA_OBJECTS

7  WHERE WNER = USER

8  AND OBJECT_NAME = 'T_PERSON'

9  AND OBJECT_TYPE = 'TABLE');

TO_CHAR

-------

200000

SQL> INSERT INTO T_PERSON

2  (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 FEFFFF

1 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 = 'AGE'

4  AND OBJ# =

5  (SELECT OBJECT_ID

6  FROM DBA_OBJECTS

7  WHERE WNER = USER

8  AND OBJECT_NAME = 'T_PERSON'

9  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 = 'AGE'

4  AND OBJ# =

5  (SELECT OBJECT_ID

6  FROM DBA_OBJECTS

7  WHERE WNER = USER

8  AND OBJECT_NAME = 'T_PERSON'

9  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 FEFFFF

1 01-1月-00     U U 000020

1 01-1月-00     U U 000400

SQL> SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), 'XXXXXX')) AS RAW(255)))

2  FROM SYS.COL$

3  WHERE NAME = 'ADDR08'

4  AND OBJ# =

5  (SELECT OBJECT_ID

6  FROM DBA_OBJECTS

7  WHERE WNER = USER

8  AND OBJECT_NAME = 'T_PERSON'

9  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 = 'ADDR08'

4  AND OBJ# =

5  (SELECT OBJECT_ID

6  FROM DBA_OBJECTS

7  WHERE WNER = USER

8  AND OBJECT_NAME = 'T_PERSON'

9  AND OBJECT_TYPE = 'TABLE');

REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),'0XXXXX'))ASRAW(255)))

------------------------------------------------------------------

000400

但是另一个麻烦的问题又出现了，就是执行TO_CHAR的时候需要指定多少个X，X的个数如果少了会报错，个数多了结果又不正确。

其中X的个数由表中总的列数来决定，具体算法为：

SQL> SELECT FLOOR(LOG(256, POWER(2, COUNT(*)))) + 1

2  FROM SYS.COL$

3  WHERE OBJ# =

4  (SELECT OBJECT_ID

5  FROM DBA_OBJECTS

6  WHERE WNER = USER

7  AND OBJECT_NAME = 'T_PERSON'

8  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  FROM

3  (

4     SELECT POWER(2, INTCOL#) NUM,

5             FLOOR(LOG(256, POWER(2, COUNT(*) OVER()))) + 1 CNT,

6             NAME

7     FROM SYS.COL$

8     WHERE OBJ# =

9     (

10             SELECT OBJECT_ID

11             FROM DBA_OBJECTS

12             WHERE WNER = USER

13             AND OBJECT_NAME = 'T_PERSON'

14             AND OBJECT_TYPE = 'TABLE'

15     )

16  )

17  WHERE NAME = 'ADDR13';

REVERSE(CAST(LTRIM(TO_CHAR(NUM,RPAD('0X',CNT*2,'X')))ASRAW(255)))

-----------------------------------------------------------------

008000

SQL> SELECT REVERSE(CAST(LTRIM(TO_CHAR(NUM, RPAD('0X', CNT * 2, 'X'))) AS RAW(255)))

2  FROM

3  (

4     SELECT POWER(2, INTCOL#) NUM,

5             FLOOR(LOG(256, POWER(2, COUNT(*) OVER()))) + 1 CNT,

6             NAME

7     FROM SYS.COL$

8     WHERE OBJ# =

9     (

10             SELECT OBJECT_ID

11             FROM DBA_OBJECTS

12             WHERE WNER = USER

13             AND OBJECT_NAME = 'T_PERSON'

14             AND OBJECT_TYPE = 'TABLE'

15     )

16  )

17  WHERE NAME = 'AGE';

REVERSE(CAST(LTRIM(TO_CHAR(NUM,RPAD('0X',CNT*2,'X')))ASRAW(255)))

-----------------------------------------------------------------

000020