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

12c新機能 In-Database Archiving

In-Database Archivingとは、いわゆる「データの論理削除」をOracle Databaseの機能として 実現する機能です。表に対して In-Database Archiving を有効にするとORA_ARCHIVE_STATEという名前のVARCHAR2型のhidden列が追加され、ORA_ARCHIVE_STATEが'0'以外の場合は、データは論理削除されたものとして扱われ、 SELECT文の結果に表示されません。

SQL> create table emp as select empno, ename from scott.emp;

表が作成されました。

SQL> desc emp
           名前       NULL?    型
           ------------ -------- --------------
    1      EMPNO                 NUMBER(4)
    2      ENAME                 VARCHAR2(10)

SQL> ALTER TABLE emp ROW ARCHIVAL;

表が変更されました。

SQL> SELECT * FROM emp;

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

12行が選択されました。

SQL> UPDATE emp SET ORA_ARCHIVE_STATE = '1' WHERE empno = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> SELECT * FROM emp;

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER
      7900 JAMES
      7902 FORD
      7934 MILLER

11行が選択されました。

論理削除されたデータ、すなわちORA_ARCHIVE_STATEに'0'以外の値が設定されたデータを表示したい場合は、 セッションの ROW ARCHIVAL VISIBILITY 属性を ALLに設定します。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

セッションが変更されました。

SQL> SELECT * FROM emp;

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

12行が選択されました。

SELECTの列リストに明示的に ORA_ARCHIVE_STATE を指定すると、 ORA_ARCHIVE_STATEの値を確認できます。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

セッションが変更されました。

SQL> SELECT emp.*, ORA_ARCHIVE_STATE FROM emp;

     EMPNO ENAME      ORA_ARCHIVE_STATE
---------- ---------- -----------------
      7369 SMITH      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行が選択されました。

なお、デフォルトの状態、すなわち、論理削除されたデータは見えない状態は、 ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE; を実行した状態に相当します。 どうやら、現行のセッションの ROW ARCHIVAL VISIBILITYがALLかACTIVEを確認する簡単な手段は無いようです。

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE; を実行した状態で、 何故 ORA_ARCHIVE_STATEに'0'以外の値が設定されたデータが表示されないかが気になったので オプティマイザのトレースを取得してみました。

----- Current SQL Statement for this session (sql_id=4u5074pdutxzs) -----
SELECT * FROM emp

    : 

=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB" FROM "RYWATABE"."EMP" "EMP" WHERE "EMP"."ORA_ARCHIVE_STATE"='0'
Objects referenced in the statement
  EMP[EMP] 92186, type = 1
Objects in the hash table
  Hash table Object 92186, type = 1, ownerid = 13711635127085985731:
    Dynamic Sampling Directives at location 1:
       dirid = 13742094096570570831, state = 3, flags = 1, loc = 1 {EC(92186)[5]}
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB" FROM "RYWATABE"."EMP" "EMP" WHERE "EMP"."ORA_ARCHIVE_STATE"='0'
kkoqbc: optimizing query block SEL$1 (#0)

        :
    call(in-use=1296, alloc=16344), compile(in-use=71208, alloc=74720), execution(in-use=2752, alloc=4032)

kkoqbc-subheap (create addr=0x7f6e0544fb60)
****************
QUERY BLOCK TEXT
****************
SELECT * FROM emp

"UNPARSED QUERY IS"の箇所からわかる通り、WHERE句に "EMP"."ORA_ARCHIVE_STATE"='0' が追加されていました。

では、どのような仕組みでWHERE句に条件が追加されたのかという話になりますが、ちょっとわかりませんでした。Oracle DatabaseでWHERE句に条件を追加する仕組みとしてよく知られるのは Virtual Private Database (VPD)です。この機能を活用しているのかと思い、VPDのポシリーを確認しましたが、該当するものは確認できませんでした。