• Oracle Database
• Oracle Database 12c R1
• 2013.09.11
• 渡部 亮太

In-Database Archiving システム生成列 SYS_NC?????$の謎

先のエントリで記載したとおり、In-Database Archivingを有効にすると、 ORA_ARCHIVE_STATEという名前のVARCHAR2型のhidden列が追加され、ORA_ARCHIVE_STATEが'0'以外の場合は、データは論理削除されたものとして扱われ、SELECT文の結果に表示されません。

しかしこれ以外にもhidden列が追加されています。

SQL> SELECT column_name, data_type, hidden_column, virtual_column, user_generated
  2    FROM USER_TAB_COLS    WHERE table_name = 'EMP' ORDER BY INTERNAL_COLUMN_ID;

COLUMN_NAME          DATA_TYP HID VIR USE
-------------------- -------- --- --- ---
EMPNO                NUMBER   NO  NO  YES
ENAME                VARCHAR2 NO  NO  YES
SYS_NC00003$         RAW      YES NO  NO  <==
ORA_ARCHIVE_STATE    VARCHAR2 YES NO  NO

SYS_??? という名前と、USER_TAB_COLS の user_generated列が'NO'であることからわかる通り、この列はシステム生成された列です。

SQL> SELECT emp.*, SYS_NC00003$, ORA_ARCHIVE_STATE FROM emp;

     EMPNO ENAME      SYS_NC00003$ ORA_ARCHIVE_STATE
---------- ---------- ------------ -----------------
      7369 SMITH      01           1
      7499 ALLEN                   0
      7521 WARD                    0
      7566 JONES                   0
      7654 MARTIN                  0
      7698 BLAKE                   0
      7782 CLARK                   0
      7839 KING                    0
      7844 TURNER                  0
      7900 JAMES                   0
      7902 FORD                    0
      7934 MILLER                  0

12行が選択されました。

検証した限りでは、初期状態では値はNULLであり、ORA_ARCHIVE_STATEに何らかの更新を加えると01という値を持つようです。

なお、実行計画を取得すると、このシステム生成列にアクセスしていることがわかります。

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  4u5074pdutxzs, child number 0
-------------------------------------
SELECT * FROM emp

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    20 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("
              EMP"."ORA_ARCHIVE_STATE",'0'),'0',NVL("EMP"."ORA_ARCHIVE_STATE",'0'),'1'
              ,"EMP"."ORA_ARCHIVE_STATE")='0')

20行が選択されました。

Predicate Informationに記載されたfilter条件のDECODEをわかりやすく書き換えると 以下のようになります。

DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),
       NULL,  NVL("EMP"."ORA_ARCHIVE_STATE",'0'),
       '0',   NVL("EMP"."ORA_ARCHIVE_STATE",'0'),
       '1',   "EMP"."ORA_ARCHIVE_STATE")

システム生成列 SYS_NCnnnnn$ が
  NULL or 0 → NVL(ORA_ARCHIVE_STATE,'0') 
               ORA_ARCHIVE_STATEが非NULLの場合 ORA_ARCHIVE_STATE
               ORA_ARCHIVE_STATEがNULLの場合 '0'
  1         → ORA_ARCHIVE_STATE

このDECODEの結果を'0'と比較することで、ORA_ARCHIVE_STATEが'0'以外のデータを検索結果から除外しています。

このシステム生成列、表の作成時にROW ARCHIVALを指定すると作成されないようです。

SQL> CREATE TABLE EMP
  2   (    "EMPNO" NUMBER(4,0),
  3        "ENAME" VARCHAR2(10)
  4         ) ROW ARCHIVAL;

表が作成されました。

SQL> col column_name format a20
SQL> col data_type   format a8
SQL> SELECT column_name, data_type, hidden_column, virtual_column, user_generated
  2    FROM USER_TAB_COLS    WHERE table_name = 'EMP' ORDER BY INTERNAL_COLUMN_ID;

COLUMN_NAME          DATA_TYP HID VIR USE
-------------------- -------- --- --- ---
ORA_ARCHIVE_STATE    VARCHAR2 YES NO  NO
EMPNO                NUMBER   NO  NO  YES
ENAME                VARCHAR2 NO  NO  YES

しかし、既存の表にROW ARCHIVALを指定すると作成されるようです。

SQL> ALTER TABLE emp NO ROW ARCHIVAL;

表が変更されました。

SQL> SELECT column_name, data_type, hidden_column, virtual_column, user_generated
  2    FROM USER_TAB_COLS    WHERE table_name = 'EMP' ORDER BY INTERNAL_COLUMN_ID;

COLUMN_NAME          DATA_TYP HID VIR USE
-------------------- -------- --- --- ---
EMPNO                NUMBER   NO  NO  YES
ENAME                VARCHAR2 NO  NO  YES

SQL> ALTER TABLE emp ROW ARCHIVAL;

表が変更されました。

SQL> SELECT column_name, data_type, hidden_column, virtual_column, user_generated
  2    FROM USER_TAB_COLS    WHERE table_name = 'EMP' ORDER BY INTERNAL_COLUMN_ID;

COLUMN_NAME          DATA_TYP HID VIR USE
-------------------- -------- --- --- ---
EMPNO                NUMBER   NO  NO  YES
ENAME                VARCHAR2 NO  NO  YES
SYS_NC00003$         RAW      YES NO  NO
ORA_ARCHIVE_STATE    VARCHAR2 YES NO  NO

うーん、ちょっと謎ですね。