不可視索引のその後

先日、不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おうという記事を書いたのだが、以下の記述に関してどうやら違う挙動となるらしいことがわかった。

INDEXヒント＋USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。

具体的には、複数の不可視索引が定義してある場合、INDEXヒントで明確に指定している不可視索引以外の不可視索引も使用されるようだ。

この部分を詳細に再検証してみたいと思う。

複数の不可視索引が存在する場合を検証する

検証環境

今回の検証で使用した環境は以下の通りである。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show user
USER is "SH"

複数の索引を同時に使用するケースを考える

B*ツリー索引は、原則として１つの問合せブロックの中で１つだけ使用される。１つのSQL文の中で同時に２つ以上のB*ツリー索引を使うためには２つ以上の問合せブロックを組み合わせる必要がある。

今回の検証では、問合せ自体はなるべく簡単にしたいので、B*ツリー索引ではなくビットマップ索引を使用する。

SH.SALES表に定義してある（ビットマップ）索引の状況を確認すると以下のようになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      VISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      VISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

基本問合せ

基本となる問合せは以下のとおり。
２つの絞り込み条件により、SALES表にアクセスする。

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

２つのビットマップ索引を使い、それぞれ絞り込んだ結果を「BITMAP AND」操作(Id=4)により両方の条件を満たす集合を作り、件数に変換して結果を得ていることがわかる。（SALES表には一切アクセスしていない。）

索引SALES_CUST_BIXを不可視にする

次に、索引SALES_CUST_BIXを不可視に変更し、同じ問合せを行ってみよう。

SQL> alter index SALES_CUST_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

CUST_IDに比べ、CHANNEL_IDのカーディナリティが低いため、CUST_IDの絞り込みに索引が使えなくなった途端、実行計画はSALES表に対する全件検索へと変わっていることがわかる。

索引SALES_CHANNEL_BIXを不可視にする

引き続き、索引SALES_CHANNEL_BIXを不可視にする。

SQL> alter index SALES_CHANNEL_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

索引SALES_CUST_BIXが使用不可（不可視）となっていることで、既に実行計画は全件検索となっているので、実行計画に変化はない。

ここまでで、SALES表の索引のうち２つを不可視に変更したことになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      INVISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      INVISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

USE_INVISIBLE_INDEXESヒントを指定する（INDEXヒントは使用しない）

ここで、USE_INVISIBLE_INDEXESヒントを指定して問合せを実行してみる。
２つの不可視索引が使えるようになるので、最初と同じ実行計画となるはずである。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   count(*) from SALES
  3  where CUST_ID    = 25939
  4  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

想定通りの結果となった。

USE_INVISIBLE_INDEXESヒントとINDEXヒントを明示的に指定する

次に、INDEXヒントでSALES SALES_CUST_BIXのみの使用を明示的に指定してみる。
INDEXヒントで使用される索引を限定することが出来るのであれば、実行計画は別のものになることが予想される。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX) */
  3   count(*) from SALES
  4  where CUST_ID    = 25939
  5  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

INDEXヒントに指定した索引とは別の索引SALES_CHANNEL_BIXも使用されていることがわかる。

つまりINDEXヒントだけでは使用される索引を特定することが出来ないことがわかった。

使用しない索引をNO_INDEXヒントで明示する

使用したくない方の不可視索引を明示的に指定するには、以下のようにNO_INDEXヒントを使う。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX)
  3             NO_INDEX(SALES SALES_CHANNEL_BIX) */
  4   count(*) from SALES
  5  where CUST_ID    = 25939
  6  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

索引SALES_CUST_BIXのみを使用する実行計画となった。

索引SALES_CUST_BIXを可視に変更する

今まで不可視だった索引SALES_CUST_BIXを可視に変更して問合せを実行してみる。
この状態では索引SALES_CHANNEL_BIXのみが使用不可である。

SQL> alter index SALES_CUST_BIX visible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

１つ前と同じ実行計画となっていることがわかる。

まとめ

USE_INVISIBLE_INDEXESヒントを指定するとSQL文単位で使える不可視索引が全てCBOの評価対象となるので、INDEXヒントで使用したい索引を特定しようとしても結果として無視される。
複数の不可視索引を作成し順番にテストするような場合、使いたくない索引をNO_INDEXヒントで指定しないと意図したテストとならない可能性があるので注意が必要である。

これらは、マニュアルにもMy Oracle Supportにも記述されていなかったので、不可視索引を使いこなす場合に覚えておきたい事実である。

今回はここまで

サイクル＆オラクル

サイクリングとデータベースを楽しむサイト

「不可視索引」タグアーカイブ

USE_INVISIBLE_INDEXESヒントについて（続編）