不可視索引とは
不可視索引とはオプティマイザから「見えない」という意味で不可視である。（12c概要 索引の使用可能性と可視性 参照）
オプティマイザに影響を与えないので、通常の索引（可視索引）作成で既存の実行計画を不用意に変えてしまうリスクを回避することができる、11g以降で実装されている機能である。
不可視索引の用途
本番運用が始まってから、「この列にインデックスが必要だ。」とか「このインデックスはどうも使われていないようなので削除しよう。」という定義変更のニーズが発生することは多々ある。
上記マニュアルには
- 索引を削除する前に削除をテストする場合
- アプリケーション全体に影響を与えることなく一時的に索引を使用する場合
という２つの使い方の例が示されているが、2.の「一時的」とはそのインデックスの有効性を確認するテストなので、（恒常的な）運用に乗せるためには
SQL> ALTER INDEX <インデックス名> VISIBLE;
として、不可視→可視とする必要があると、マニュアルや多くのブログ記事等には書いてある。
というようなことを先日あるプロジェクトの人に話したところ「可視化した時点でアプリケーション全体に影響を与える可能性があるので望ましくない。SQL単位で可視化を制御する方法はないのか？」と質問された。
確かにもっともな意見である。普段いろいろ教える機会が多いのだが、実際に使う側の目線で本質的な問いを投げかけられるとハッとさせられる。
SQL単位でとなると答えはヒント句による制御しかない。
不可視索引関連のヒント
結論から先に言うと、不可視索引関連のヒントは
- USE_INVISIBLE_INDEXES（NO_USE_INVISIBLE_INDEXES）
であるが、残念ながらSQLリファレンス・マニュアルにはこのヒントに関する記述がない。
（以前、Oracleバージョンによるヒント句の変遷 という記事を書いたのでそちらを参照してもらいたい。）
ヒント句を検証してみた
不可視索引の作成
検証を行うためにEMP表のHIRE_DATE列に不可視索引を作成する。
SQL> create index EMP_HIRE_DATE_IX on EMPLOYEES (HIRE_DATE) invisible; Index created. SQL> select TABLE_NAME,INDEX_NAME,VISIBILITY from user_indexes 2 where VISIBILITY != 'VISIBLE' 3 order by TABLE_NAME,INDEX_NAME; TABLE_NAME INDEX_NAME VISIBILIT ------------------------------ ------------------------------ --------- EMPLOYEES EMP_HIRE_DATE_IX INVISIBLE
基本動作（不可視索引はそのままでは使われない）
不可視索引はデフォルトではオプティマイザに使用されないので、条件検索は全件検索からのフィルタ処理となる。（Id=1）
SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
2 where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- -------------------- ------------------------- ----------------
167 Amit Banda 2008/04/21 00:00
173 Sundita Kumar 2008/04/21 00:00
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 27 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更
不可視索引を使用するためにはALTER SESSIONコマンドにより、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更する。
（ALTER SYSTEMコマンドによりインスタンスで使用可能に変更することもできるが、不可視索引として作成する意味がないので現実的ではない。）
SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE SQL> alter session set optimizer_use_invisible_indexes=true; Session altered. SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean TRUE
この状態で先ほどの問合せを実行すると、使用されなかったインデックスEMP_HIRE_DATE_IXが使用されるようになったことがわかる。
SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
2 where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- -------------------- ------------------------- ----------------
167 Amit Banda 2008/04/21 00:00
173 Sundita Kumar 2008/04/21 00:00
Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_HIRE_DATE_IX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
ヒント句を試してみる
ヒント句を試す前に、セッションを再接続することでクリアし、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータがデフォルトの「FALSE」に戻っていることを確認する。
SQL> conn hr/hr Connected. SQL> show parameter optimizer_use_invisible_indexes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE
INDEXヒントのみ
まず、INDEXヒントにより不可視索引を指定した場合どのような挙動になるのかを確認する。
SQL> select /*+ INDEX (employees emp_hire_date_ix) */
2 EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
3 where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- -------------------- ------------------------- ----------------
167 Amit Banda 2008/04/21 00:00
173 Sundita Kumar 2008/04/21 00:00
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 27 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
オプティマイザがこのインデックスを使用するようになっていないため、ヒント句でインデックス名を指定したとしても無視されることがわかる。
USE_INVISIBLE_INDEXESヒントのみ
次に、USE_INVISIBLE_INDEXESヒントをヒント句で指定してみる。
SQL> select /*+ USE_INVISIBLE_INDEXES */
2 EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
3 where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- -------------------- ------------------------- ----------------
167 Amit Banda 2008/04/21 00:00
173 Sundita Kumar 2008/04/21 00:00
Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_HIRE_DATE_IX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
今度は、インデックスを使用するようになった。
INDEXヒント＋USE_INVISIBLE_INDEXESヒント
基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。 （この部分は事実と異なる。NO_INDEXヒントにより使いたくない索引も明示的に指定する必要がある。 USE_INVISIBLE_INDEXESヒントについて（続編） 参照）
SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX) */
2 EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
3 where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- -------------------- ------------------------- ----------------
167 Amit Banda 2008/04/21 00:00
173 Sundita Kumar 2008/04/21 00:00
Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 27 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_HIRE_DATE_IX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
SQL文の可読性を向上させるために、使用したいインデックスを明示的に指定した方がよいかもしれない。
前々回、前回とWHERE句とインデックスの関係を調査する
という２つの記事を書いたが、新しく作成するインデックスが及ぼす影響を網羅性を担保しつつ調査することは、インデックスの数が多いほど大変な作業である。
ヒント句で有効にした不可視索引はアプリケーション全体に影響を与えることなく性能を改善することができる。不可視索引を使うには原則としてヒント句を共に使うべきであるというのが私の提言である。
今日はここまで。
ピンバック: USE_INVISIBLE_INDEXESヒントについて（続編） | サイクル＆オラクル