Oracleのバーチャルインデックス

バーチャルインデックスを使うと、ディスクスペースや作成時間、他のアプリケーション（セッション）を
気にせずにインデックス追加時のアクセスプランなどを確認することができる。

テーブル定義

SQL> desc person
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 P_ID                                      NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

バーチャルインデックスの作成

create indexのオプションにnosegmentを指定することでバーチャルインデックスになる

create index person_name_index on person(name) nosegment;

バーチャルインデックスを使ってみる

通常は、バーチャルインデックスは使用されない。

  1* select * from person where name = 'a'

実行計画
----------------------------------------------------------
Plan hash value: 1493655343

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    65 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PERSON |     1 |    65 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='a')

_use_nosegment_indexesパラメータの値をtrueに変更することで、現在のセッションのみバーチャルインデックスが有効となる。

SQL> alter session set "_use_nosegment_indexes"=true;

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

バーチャルインデックスを有効に変更した後にためしてみると、インデックスが参照されるようになる。

  1* select * from person where name = 'a'

実行計画
----------------------------------------------------------
Plan hash value: 966497106

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |    65 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PERSON            |     1 |    65 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PERSON_NAME_INDEX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------