函式索引產生隱藏列

前兩天在進行LOGMNR操作的時候發現了空的列名，經檢查發現是函式索引造成的隱藏列。

 

 

由於需要處理一些已經提交的資料，且提交時間超過了可以閃回的時間，因此採用LOGMNR來處理。處理過程中，發現得到的SQL語句中，包含了空列的情況，情況類似下面的例子：

SQL> CONN SYS@YTK102 AS SYSDBA
輸入口令:
已連線。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

會話已更改。

SQL> SELECT GROUP#, FIRST_TIME FROM V$LOG;

    GROUP# FIRST_TIME
---------- -------------------
         1 2008-01-18 22:35:57
         2 2008-01-20 18:58:37
         3 2008-01-16 23:56:50

SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# = 2;

MEMBER
--------------------------------------------------------------
E:\ORACLE\ORADATA\YTK102\REDO02.LOG

SQL> ALTER SYSTEM SWITCH LOGFILE;

系統已更改。

SQL> EXEC DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK102\REDO02.LOG', DBMS_LOGMNR.NEW)

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL 過程已成功完成。

SQL> SELECT SQL_UNDO FROM V$LOGMNR_CONTENTS
  2  WHERE SEG_NAME = 'T_LOGMNR';

SQL_UNDO
--------------------------------------------------------------------------
insert into "YANGTK"."T_LOGMNR"("ID","NAME","") values ('1','A',NULL);
insert into "YANGTK"."T_LOGMNR"("ID","NAME","") values ('2','B',NULL);
insert into "YANGTK"."T_LOGMNR"("ID","NAME","") values ('3','C',NULL);

可以看到T_LOGMNR表後面跟了一個空的列，而這個列的值也是NULL。

檢查表結構：

SQL> DESC YANGTK.T_LOGMNR
 名稱                                          是否為空? 型別
 --------------------------------------------- -------- ----------------
 ID                                            NOT NULL NUMBER
 NAME                                                   VARCHAR2(30)

SQL> SELECT TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLUMNS
  2  WHERE TABLE_NAME = 'T_LOGMNR'
  3  AND WNER = 'YANGTK';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
T_LOGMNR                       ID
T_LOGMNR                       NAME

從資料字典中找不到這個空列的資訊，直接查詢COL$表：

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS
  2  WHERE OBJECT_NAME = 'T_LOGMNR'
  3  AND WNER = 'YANGTK';

 OBJECT_ID
----------
     53636

SQL> SELECT NAME FROM COL$ WHERE OBJ# = 53636;

NAME
------------------------------
ID
NAME
SYS_NC00003$

從COL$中可以看到這個隱藏列了，下面進一步檢查，這個隱藏列到底是什麼：

SQL> SELECT NAME, DEFAULT$ FROM COL$ WHERE OBJ# = 53636;

NAME                           DEFAULT$
------------------------------ --------------------------------------------
ID
NAME
SYS_NC00003$                   DECODE("ID",1,"NAME"||'1',"NAME"||'2')

目前已經可以看出這個列是一個基於基礎列的函式表示式，對比函式索引列：

SQL> SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS FROM DBA_INDEXES
  2  WHERE TABLE_NAME = 'T_LOGMNR'
  3  AND TABLE_OWNER = 'YANGTK';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
SYS_C005983                    NORMAL                      UNIQUE
IND_T_LOGMNR_NAME              FUNCTION-BASED NORMAL       NONUNIQUE

SQL> SELECT COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS
  2  WHERE TABLE_NAME = 'T_LOGMNR'
  3  AND TABLE_OWNER = 'YANGTK'
  4  AND INDEX_NAME = 'IND_T_LOGMNR_NAME';

COLUMN_EXPRESSION
----------------------------------------------------------------------------
DECODE("ID",1,"NAME"||'1',"NAME"||'2')

可以看到，這個隱藏列就是函式索引產生的。