前回のおさらい
前回はすぐに使える！DBデータの論理削除(3/4) 論理削除のしくみ(後編)ということで、In-Database Archivingが、ユーザから入力するSQLを変更せず、どのように削除フラグが設定された行を除外するのかについて紹介しました。
今回は、In-Database Archivingによってテーブルに追加された、削除フラグ管理用の非表示列ora_archive_stateに索引を設定した場合の動作について紹介します。
前提として、今回も引き続きOracle Databaseの知識がある人向け(マニア向け)の内容になっているかと思いますが、どうぞよろしくお願いいたします。
ora_archive_state列に索引を設定する
In-Database Archivingではテーブルにora_archiving_state列を追加します。
この列は、論理削除済みの行も表示する設定にしない限り、常にwhere句に条件として指定される列となります。
また、削除フラグは論理削除時に0以外に変更する以外、基本的には変更しないため、更新頻度の低い列であると言えます。
そこで、このora_archiving_state列に索引に追加するとどのような動作となるのかを確認してみましょう。
今回も実際にコマンドとその結果を見ながら進めていきたいと思います。
もし実際に検証用に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回、第3回のコマンドを実行している前提のコマンド及び実行結果となっています。
それでは、実際にコマンドを見ていきましょう！
今回は、DatabaseがSQLを実行した際に、どのような処理を実行したのかをみるため、SQLを実行した後、実行計画を取得します。
この実行計画を確認することにより、処理で使用された検索方法(全表走査なのか、何れかの索引を使用したのか)を確認することができます。
まずは、In-Database Archivingが無効の状態を確認します。
ちなみに、In-Database Archivingは一度無効にすると、ora_archive_state列の情報は消えてしまいます。
そのため、論理削除済みの行は全て通常の状態に戻り、DMLから除外されなくなりますので、注意してください。
# 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> set pages 9999 lin 200
# In-Database Archivingを無効化します。
SQL> Alter table EMP no row archival;
表が変更されました。
# 毎回実行時の条件をそろえるため、
# 共有プールとバッファキャッシュをフラッシュします。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> alter system flush buffer_cache;
システムが変更されました。
# select文を実行し、実行計画を取得します。
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
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID gr96sba0x5n9a, child number 0
-------------------------------------
select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654)
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 195 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7521 OR "EMPNO"=7566 OR
"EMPNO"=7654))
21行が選択されました。
実行時の詳細なコストなどは今回は省略して、赤文字の行に注目します。
索引PK_EMP(主キーEMPNOに設定された索引)が使用されています。
次に、In-Database Archivingを有効化して同様に実行計画を取得します。
# In-Database Archivingを有効化します。
SQL> Alter table EMP row archival;
表が変更されました。
# 削除フラグを設定します。
SQL> update emp set ora_archive_state = '1' where empno in (7369, 7521, 7782);
3行が更新されました。
# 毎回実行時の条件をそろえるため、
# 共有プールとバッファキャッシュをフラッシュします。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> alter system flush buffer_cache;
システムが変更されました。
# select文を実行し、実行計画を取得します。
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> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID gr96sba0x5n9a, child number 0
-------------------------------------
select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654)
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 43 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00009$",0)),NULL,NVL("EMP"."O
RA_ARCHIVE_STATE",'0'),'0',NVL("EMP"."ORA_ARCHIVE_STATE",'0'),'1',"EMP"."ORA_AR
CHIVE_STATE")='0')
3 - access(("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7521 OR "EMPNO"=7566 OR
"EMPNO"=7654))
24行が選択されました。
In-Database Archiving無効時と同様に索引PK_EMPを使用していることが解ります。
ここで、where句にはEMPNOだけではなく、ora_archive_stateも使用しているため、このora_archive_stateにも索引を設定するとどうなるでしょうか。
今回はwhere句にはEMPNO列も含まれているため、この2つを含む索引を設定してみましょう。
# 索引を作成します。
SQL> create index PK_EMP_ARCH on scott.emp (EMPNO, ORA_ARCHIVE_STATE);
索引が作成されました。
# 毎回実行時の条件をそろえるため、
# 共有プールとバッファキャッシュをフラッシュします。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> alter system flush buffer_cache;
システムが変更されました。
# select文を実行し、実行計画を取得します。
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> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID gr96sba0x5n9a, child number 0
-------------------------------------
select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654)
Plan hash value: 778553705
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 215 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PK_EMP_ARCH | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access((("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7521 OR "EMPNO"=7566 OR
"EMPNO"=7654)) AND "EMP"."ORA_ARCHIVE_STATE"='0')
21行が選択されました。
新たに作成した索引PK_EMP_ARCHが使用されました。
今回、索引を指定するヒント句は使用していないため、Databaseによって、ora_archive_state列も含めた索引を使用することが効率が良いと判断されたことが解ります。
最後に、索引PK_EMP_ARCHが存在する状態で、削除フラグが設定された行も表示する設定で、同様にselect文を実行してみましょう。
今度はwhere句にora_archive_state列は含まれないはずですが、実行計画はどうなるでしょうか。
# 削除フラグが設定された行も表示するよう設定します。
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
セッションが変更されました。
# 毎回実行時の条件をそろえるため、
# 共有プールとバッファキャッシュをフラッシュします。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> alter system flush buffer_cache;
システムが変更されました。
# select文を実行し、実行計画を取得します。
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
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID gr96sba0x5n9a, child number 0
-------------------------------------
select * from scott.emp where empno in (7369, 7499, 7521, 7566, 7654)
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 195 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7521 OR "EMPNO"=7566 OR
"EMPNO"=7654))
21行が選択されました。
ora_archive_state列を含まない、索引PK_EMPが使用されています。
ora_archive_state列は不要とDatabaseが判断し、この列を含まない索引PK_EMPが使用されたと考えられます。
つまり、削除フラグ管理用のora_archive_state列を含めた索引を作成しておけば、通常のDatabaseのSQL実行時の動作と同様、どの索引を使用するかを自動で判断し、実行してくれることが解ります。
まとめ
今回は、12cの新機能の一つである、In-Database Archivingについて紹介をさせて頂きました。
当社では12cの導入実績も多数ございますので、今回紹介させて頂いた機能に限らず、何かご不明な事や、ご興味を持たれたことがございましたら、お気軽にお問合せ下さいませ。
投稿者プロフィール
