SQL
oracle

Oracle Database 12c の論理削除機能を試す

More than 1 year has passed since last update.

論理削除

RDBMSのテーブルから不要なレコードを削除するにははDELETE文を実行します。しかし、アプリケーション上の不要なレコードでも他の用途(監査など)で使いたいという要求がある場合があります。このような要求に対して、テーブルからレコードを削除(物理削除)せず他の方法で「削除されたように見せる」手法がとられます。

「削除フラグ」列や「有効フラグ」列

テーブルにレコードの有効性を示すフラグを示す列を指定します。この方法では常に有効なレコードを取得するためにWHERE句に条件を付ける必要があり、また主キー制約が重ならないようにする必要があることから列設計が複雑になるという欠点があります。

不要レコードの移動

不要レコードが発生した時点で、他のテーブルへレコードを移動します。
この方法は有効なレコードを持つテーブルは小さく維持できます。しかしデータの移動を行うコードの追加がある点と、テーブルが分割されるために有効レコードと無効レコードを一緒に取得する場合にはUNION句で連結する必要がある点が欠点です。

In-Database Archiving

Oracle Database 12c (12.1) では、フラグによる論理削除の実装を簡単にするIn-Database Archiving機能が提供されました。
この機能は簡単に言うと「不要になったレコードを見えなくする」技術です。テーブルに追加された特殊な列を更新することで、テーブル内の特定のレコードはSQL文から「見えなく」なります。このため常にテーブル上のレコードから有効なレコードだけを取得するWHERE句を記述する必要がなくなります。

有効化

In-Database Archiving機能を使用するためには、テーブルに対してALTER TABLE ROW ARCHIVAL文を実行します(CREATE TABLE文でも可能)。
この文を実行すると、テーブルにORA_ARCHIVE_STATE列(VARCHAR2(1))列が追加されます。この列の値が'0' (0x30) 以外のレコードは通常のSQLからは参照できなくなります。またSYS_NC{99999}$列(RAW(1))列も追加されますが用途は不明です。

実行例

テーブルの作成とIn-Database Archivingの準備を行います。arch1テーブルはアプリケーション用のテーブルです。

SQL> CREATE TABLE arch1(c1 NUMBER, c2 VARCHAR2(10)) ;
Table created.

SQL> ALTER TABLE arch1 ROW ARCHIVAL ;
Table altered.

データを格納し、ORA_ARCHIVE_STATE列を更新することでレコードが参照できなくなることを確認します。

SQL> INSERT INTO arch1 VALUES (100, 'valid data') ;
1 row created.

SQL> SELECT * FROM arch1 ;

        C1 C2
---------- ----------
       100 valid data

SQL> UPDATE arch1 SET ORA_ARCHIVE_STATE='1' WHERE c1=100 ;
1 row updated.

SQL> SELECT * FROM arch1 ;
no rows selected

内部構造

ALTER TABLE ROW ARCHIVAL文を実行したテーブルをSQL文で検索すると、自動的にWHERE句に「ORA_ARCHIVE_STATE='0'」が付与されます。

SQL> SELECT * FROM arch1 ;
no rows selected

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0jsp8t1360byk, child number 0
-------------------------------------
SELECT * FROM arch1

Plan hash value: 4232069424

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

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

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

不要なレコードを参照するには

不要としたレコードも必要に応じて参照することができます。ALTER SESSION SET ROW ARCHIVAL VISIBILITY文により、制御を行うことができます。SET ROW ARCHIVAL VISIBILITY = ALL を実行すると、不要とマークしたレコードまで参照することができます。SET ROW ARCHIVAL VISIBILITY = ACTIVEに設定すると不要レコードは参照できなくなります。これはデフォルトの動作です。

SQL> SELECT * FROM arch1 ;
no rows selected

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL ;
Session altered.

SQL> SELECT * FROM arch1 ;

        C1 C2
---------- ----------
       100 valid data

注意点

この機能を使うことで、不要なレコードをテーブルから隠すことができるようになります。しかし、テーブルに付与された制約(主キー、一意キー等)は有効なままです。このため、テーブルを検索するとレコードが無いのに、INSERT文やUPDATE文がエラーになるという事象が発生します。

確認するためにまずIn-Database Archiving機能を有効にした主キー付きのテーブルarch2を作成し、1レコード追加します。次にORA_ARCHIVE_STATE列を更新して、INSERT文で格納したレコードを参照できなくしています。

SQL> CREATE TABLE arch2 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(10)) ROW ARCHIVAL ;
Table created.

SQL> INSERT INTO arch2 VALUES (100, 'data') ;
1 row created.

SQL> SELECT * FROM arch2 ;

        C1 C2
---------- ----------
       100 data

SQL> UPDATE arch2 SET ORA_ARCHIVE_STATE='1' WHERE c1=100 ;
1 row updated.

テーブルを検索すると、レコードは1件もありません。このため別のINSERT文を実行してレコードを追加しようとしていますが、制約違反が発生してエラーが発生しています。

SQL> SELECT * FROM arch2 ;
no rows selected

SQL> INSERT INTO arch2 VALUES (100, 'data2') ;
INSERT INTO arch2 VALUES (100, 'data2')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0011159) violated