はじめまして

今回から連載「すぐに使える！DBデータの論理削除」を担当します。
全4回を予定しておりますので、最後までお付き合い頂けると幸いです。

まずは簡単に自己紹介させて頂きます。
私は2014年に新卒で入社し、主にOracle DBを中心とした案件の設計構築を行ったり、コンサルとして各企業様をご支援しています。
また、社内の勉強会の企画、運営などを通し、技術の平準化に注力しています。

写真撮影やゲーム、ダーツが趣味ですが、最近、休日は家に引きこもりがちなのでもっと写真を撮りに遠出をしたいです。

宜しくお願い致します。

Oracle Database でデータの論理削除

今回はOracle Database 12cR1で追加された新機能、In-Database Archivingについて取り上げます。この機能のポイントは以下の2点です。

• テーブルの再作成等を行わずにデータの論理削除を実現できること
• テーブル内のデータやSQL文を変更せずに、論理削除済みのレコードの表示/非表示を選択できること

前提として、少しOracle Databaseの知識がある人向け(マニア向け)の内容になっているかと思いますが、どうぞよろしくお願いいたします。

そもそも論理削除って何？

「Oracle Databaseでの論理削除が～」といった内容の前に、まず「論理削除」とは何かご存知でしょうか。 Databaseにおける「削除」とは、次の2つがあります。

• 物理削除
  実際にSQLでDeleteすることをさし、Databaseからも削除されます。そのため、削除したデータを復旧するためには、特別な条件や手順が必要になり、削除したデータを参照することもできません。
• 論理削除
  実際にはDatabaseからは削除せず、データに「削除された」という目印(削除フラグと言います)を設定し、通常のデータ参照時は削除フラグがあるデータは表示しないことで、ユーザには削除しているかのようにふるまうことをさします。

これだけでは少しわかりにくいと思いますので、論理削除のメリットとデメリットをまとめてみます。

• メリット
  ①誤って削除したデータを簡単に復元できる
  ②データに、削除済みのデータも含めた通し番号を付けたい場合に、削除済みデータも参照可能なため、通し番号の管理が容易
  
• デメリット
  ①削除フラグを使用するため、テーブルに削除フラグ用の列を追加する必要がある
  ※既に運用中のテーブルがある場合は、そのテーブルに列を追加する必要がある
  ②通常、参照の際に削除フラグ付きのデータを表示しないよう、SQLを作成する必要がある
  ③データが増加し続けるため、Databaseのパフォーマンスを損なう、または容量を圧迫する恐れがある

物理削除でも削除したデータの復元はバックアップリカバリなどの機能で実現可能ですが、多くの場合「ある特定の時点に戻す」ことになってしまい、たくさんの処理の内、途中で誤ってデータを削除してしまった場合、削除処理以降の作業を再実行する仕組みが必要となってしまいます。

論理削除であれば、削除フラグを削除するだけのため、削除処理以降の作業が取り消されることはありません。
また、例えば企業の社員リストの社員番号やメールアドレスなど、一度使用したデータは削除した後も二度と重複させたくない場合、論理削除であれば実際のデータとしては削除されていないため、容易に管理が可能です。

デメリットのうち、3つ目の容量の増加については、削除フラグ設定後、一定の期間が経過したものは物理削除を行うことで、ある程度の対策が可能です。
そのため、論理削除を実際に運用していく中では、SQLやテーブルの構成変更や管理が課題となります。

In-Database ArchivingならSQLやテーブルの構成変更が不要！

Oracle DatabaseのIn-Database Archivingでは、論理フラグの管理用にSQLやテーブルの構成を変更する必要がありません。
テーブルに対しIn-Database Archiving機能を有効化するのみで、論理削除の運用が可能となります。

実際には、以下のようになります。

# サンプルのテーブル「testtable」を参照します。
SQL> select * from test.testtable where col1 between 101 and 120;
       COL1       COL2
 ---------- ----------
        101          1
        102          2
        103          3
        104          4
        105          5
        106          6
        107          7
        108          8
        109          9
        110         10
        111         11
        112         12
        113         13
        114         14
        115         15
        116         16
        117         17
        118         18
        119         19
        120         20

20行が選択されました。

# テーブル「testtable」の定義を確認します。
SQL> desc test.testtable
  名前                         NULL?    型
  ---------------------------- -------- ----------------------------
  COL1                         NOT NULL NUMBER
  COL2                                  NUMBER

# テーブル「testtable」に対しIn-Database Archivingを有効化します。
SQL> ALTER TABLE test.testtable ROW ARCHIVAL;

表が変更されました。

# テーブル「testtable」の定義を確認します。
SQL> desc test.testtable
  名前                         NULL?    型
  ---------------------------- -------- ----------------------------
  COL1                         NOT NULL NUMBER
  COL2                                  NUMBER
# 上記の通り、ユーザから見たテーブル定義は特に変化がありません。

# 削除フラグを設定します。
SQL> UPDATE test.testtable SET ORA_ARCHIVE_STATE = '1' 
       WHERE col2 = 5;

1000行が更新されました。

SQL> UPDATE test.testtable SET ORA_ARCHIVE_STATE = '1' 
      WHERE col2 = 10;

1000行が更新されました。

SQL> UPDATE test.testtable SET ORA_ARCHIVE_STATE = '1' 
      WHERE col2 = 15;

1000行が更新されました。

SQL> UPDATE test.testtable SET ORA_ARCHIVE_STATE = '1' 
      WHERE col2 = 20;

1000行が更新されました。

SQL> commit;

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

# 再度サンプルのテーブル「testtable」を参照します。
# 実行するSQLに変更はありません。
SQL> select * from test.testtable where col1 between 101 and 120;

       COL1       COL2
 ---------- ----------
        101          1
        102          2
        103          3
        104          4
        106          6
        107          7
        108          8
        109          9
        111         11
        112         12
        113         13
        114         14
        116         16
        117         17
        118         18
        119         19

16行が選択されました。
# col2の値が5、10、15、20の列の列が表示されなくなり、
# 結果の行数も減少しています。

# 論理削除したデータを参照するには、以下のコマンドを実行します。
# ※実行したセッションのみで参照できるようになります。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

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

# 再度サンプルのテーブル「testtable」を参照します。
SQL> select * from test.testtable where col1 between 101 and 120;

       COL1       COL2
 ---------- ----------
        101          1
        102          2
        103          3
        104          4
        105          5
        106          6
        107          7
        108          8
        109          9
        110         10
        111         11
        112         12
        113         13
        114         14
        115         15
        116         16
        117         17
        118         18
        119         19
        120         20

20行が選択されました。
# 削除フラグを設定したデータも表示されるようになりました。

SQLやテーブルの変更作業無く、論理削除が実現できました。
論理削除を使う場合、テーブルの列の追加やSQLへのWHERE句の追加など、変更しなければないものが多いですが、In-Database Archiving機能ではそれらの考慮は不要となります。

次回はIn-Database Archivingによる論理削除が、実際にはどのように動作しているのかを紹介します。