前回のおさらい

前回はすぐに使える！DBデータの論理削除(2/4) 論理削除のしくみ(前編)ということで、In-Database Archivingが、ユーザから見たテーブル定義を変更せず、どのように削除フラグを管理するのかについて紹介しました。

今回は、ユーザが入力するSQLを変更せずに、削除フラグが設定されている行をselect文の結果からどのように除外するかを紹介します。

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

どのように削除フラグの設定された行を除外しているのか？

削除フラグは非表示のora_archive_state列に格納されていました。
ですが、ユーザが入力するSQLには、「ora_archive_stateが0ではない」といった条件は付いていません。
それでは、ora_archive_stateが0ではない行をどのように除外しているのでしょうか。

今回も実際にコマンドとその結果を見ながら進めていきたいと思います。
もし実際に検証用にDBを使用できるのであれば、是非実際に試してみてください！

まずは今回検証で使用するDB環境についてご紹介します。

DBバージョン	12.2.0.1.0
Oracle DB所有者ユーザ	oracle
ORACLE_BASE	/u01/app/oracle
ORACLE_HOME	/u01/app/oracle/product/12.2.0/dbhome_1
リスナー名	LISTENER
DBインスタンス名	orcl
Oracle SID	orcl
PDB名（※1）	orclpdb
PDB接続用接続記述子(※1)	orclpdb

※1
12c以降、Oracle DatabaseではCDBアーキテクチャを使用した構成が推奨となっています。
そのため、今回はPDBを一つだけ作成したシングルテナント構成としています。
シングルテナント構成であれば、CDBアーキテクチャの使用に特別なライセンスは必要ありません。
(PDBを複数作成するとOracle Multitenantオプションが必要となります。)
CDBアーキテクチャについては、後日、本ブログでも取り上げる予定です！

DBの構成によっては、実行結果が異なる場合があります。
また、第2回のコマンドを実行している前提のコマンド及び実行結果となっています。

それでは、実際にコマンドを見ていきましょう！

今回は少し複雑な作業をするため、手順簡略化のためscottユーザにDBA権限を付与します。

# scottユーザにDBA権限を付与します。
[oracle$] sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 金 3月 23 20:02:39 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。

SQL> alter session set container = orclpdb;

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

SQL> grant dba to scott;

権限付与が成功しました。

SQL> quit
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの接続が切断されました。

今回は、DatabaseがSQLを実行する際、どのように処理を行うかを決定するCost Base Optimizerのトレースを取得し、select時の動作を確認します。
はじめに、削除フラグが設定された行も表示する状態でトレースを取得します。

# PDBにログインします
[oracle$] sqlplus scott/tiger@orclpdb

SQL*Plus: Release 12.2.0.1.0 Production on 金 3月 23 20:06:16 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

最終正常ログイン時間: 金 3月  23 2018 20:06:06 +09:00


Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。

# 共有プールをフラッシュします。
SQL> alter system flush shared_pool;

システムが変更されました。

# 論理削除行も表示する設定にします。
SQL> alter session set row archival visibility = all;

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

# トレースの取得を設定します。
SQL> alter session set events '10053 trace name context forever, level 1';

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

SQL> select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK	      7902 80-12-17	   800
	20

      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300
	30

      7521 WARD       SALESMAN	      7698 81-02-22	  1250	      500
	30


     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER	      7839 81-04-02	  2975
	20

      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400
	30


# トレースの取得を終了します。
SQL> alter session set events '10053 trace name context off';

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

SQL> quit
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの接続が切断されました。

# トレースファイルを確認します。
[oracle$] ls -ltr $ORACLE_BASE/diag/rdbms/orcl/orcl/trace
[略]
-rw-r-----. 1 oracle oinstall 107715  3月 27 01:21 orcl_ora_24420.trc
[略]
# トレース取得時間帯に更新されている、「orcl_ora」から始まるtrcファイルのファイル名を確認します。

上記で確認したトレースファイルの内容を確認します。
ファイル内に、「Final query after transformations:******* UNPARSED QUERY IS *******」と出力されている個所の直後のSQLを確認します。

上記実行例では、以下のような出力が確認できます。
「SELECT “EMP”.”EMPNO” “EMPNO”,”EMP”.”ENAME” “ENAME”,”EMP”.”JOB” “JOB”,”EMP”.”MGR” “MGR”,”EMP”.”HIREDATE” “HIREDATE”,”EMP”.”SAL” “SAL”,”EMP”.”COMM” “COMM”,”EMP”.”DEPTNO” “DEPTNO” FROM “SCOTT”.”EMP” “EMP” WHERE “EMP”.”EMPNO”=7369 OR “EMP”.”EMPNO”=7499 OR “EMP”.”EMPNO”=7521 OR “EMP”.”EMPNO”=7566 OR “EMP”.”EMPNO”=7654」

次に、削除フラグが設定された行は表示しない設定で同様にトレースを取得します。

# PDBにログインします
[oracle$] sqlplus scott/tiger@orclpdb

SQL*Plus: Release 12.2.0.1.0 Production on 金 3月 23 20:06:16 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

最終正常ログイン時間: 金 3月  23 2018 20:06:06 +09:00


Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。

# 共有プールをフラッシュします。
SQL> alter system flush shared_pool;

システムが変更されました。

# 論理削除行も表示しない設定にします。
SQL> alter session set row archival visibility = active;

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

# トレースの取得を設定します。
SQL> alter session set events '10053 trace name context forever, level 1';

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

SQL> select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654);

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300
	30

      7566 JONES      MANAGER	      7839 81-04-02	  2975
	20

      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400
	30

# トレースの取得を終了します。
SQL> alter session set events '10053 trace name context off';

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

SQL> quit
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの接続が切断されました。

# トレースファイルを確認します。
[oracle$] ls -ltr $ORACLE_BASE/diag/rdbms/orcl/orcl/trace
[略]
-rw-r-----. 1 oracle oinstall 107715  3月 27 01:21 orcl_ora_24328.trc
[略]
# トレース取得時間帯に更新されている、「orcl_ora」から始まるtrcファイルのファイル名を確認します。

 

上記で確認したトレースファイルの内容を確認します。
ファイル内に、「Final query after transformations:******* UNPARSED QUERY IS *******」と出力されている個所の直後のSQLを確認します。

上記実行例では、以下のような出力が確認できます。
「SELECT “EMP”.”EMPNO” “EMPNO”,”EMP”.”ENAME” “ENAME”,”EMP”.”JOB” “JOB”,”EMP”.”MGR” “MGR”,”EMP”.”HIREDATE” “HIREDATE”,”EMP”.”SAL” “SAL”,”EMP”.”COMM” “COMM”,”EMP”.”DEPTNO” “DEPTNO” FROM “SCOTT”.”EMP” “EMP” WHERE “EMP“.”ORA_ARCHIVE_STATE”=’0′ AND (“EMP”.”EMPNO”=7369 OR “EMP”.”EMPNO”=7499 OR “EMP”.”EMPNO”=7521 OR “EMP”.”EMPNO”=7566 OR “EMP”.”EMPNO”=7654)」

削除フラグ付きの行を非表示にした場合、入力したSQL文には存在しない、「“EMP”.”ORA_ARCHIVE_STATE”=’0′」という条件が追加されていることが解ります。

Oracle DatabaseのCBOは、論理的に同じ意味を持たせたままSQLを変換する機能があり、上記はCBOによってSQLが書き換えられた場合の、変換後のSQLを確認しています。今回は論理的に同じ意味を保っていないため、この機能による変換とは異なると思われますが、ユーザが入力したSQLはDB内部で変換され、where句に論理フラグが設定されていない、という条件が追加されていることが解ります。

以上のことから、In-Database Archivingでは、テーブルに非表示列ora_archive_stateを追加して削除フラグを管理し、テーブルアクセス時は、DB内部でwhere句にora_archive_state = 0を追加することで、ユーザにSQLやテーブルの構成変更を意識させることなく、論理削除を実現していることが解ります。

本来であれば、ユーザがテーブルに列を追加し、SQLも作り替えなければいけない論理削除の導入を、DBが自動的に行ってくれる、便利な機能ですね。

Oracle Databaseで論理削除を実装するのであれば、テーブルやSQLの構造について特別な考慮が不要な本機能は効果的なのではないでしょうか。

次回は、削除フラグ管理用の列が増えたことに対し、索引を追加するとどのような動作になるか、確認します。

投稿者プロフィール

技術チーム

DBひとりでできるもんを盛り上げるべく、技術チームが立ち上がり早6年。ひとりでできるもんと言いつつ、技術者が読んでプッとなるような、極めてピンポイントでマニアックな技術ネタを執筆しています！

• Oracle Cloud2024年11月21日コネクタ・ハブを利用してOCIのログイン通知を行う
• Dbvisit Standby2024年11月20日【Dbvisit Standby】チュートリアル_再同期（スタンバイDBとの同期復旧）
• Dbvisit Standby2024年11月19日【Dbvisit Standby】チュートリアル_Standbyフェイルオーバーテスト
• Dbvisit Standby2024年11月12日【Dbvisit Standby】チュートリアル_自動同期の有効化/無効化