不可視索引 - うんちくメモ

■不可視索引を利用したインデックス検証手順メモ

・不可視索引として作成後、セッション限定で実行計画をチェックした上で通常索引へ切り替える事で事前にインデックス作成による影響範囲を検証する

1.新規インデックス作成時などにinvisibleパラメータを付与して不可視索引として作成
※「invisible」を付与する。デフォルトはvisible

create index test_idx01 on test(col1) invisible;

2.セッション限定で不可視索引を有効化
※デフォルトは「false」

alter session set optimizer_use_invisible_indexes=true;

3.作成したインデックスが問題ない場合にvisibleに変更することでシステムで有効化
※不可視化する場合には「invisible」を指定

alter index test_idx visible;

・索引の不可視設定確認
dba_indexesより「visibility」列を確認する

select index_name,visibility fromm dba_indexes;

※2018/1/29追記修正
※不可視索引に対するヒント句の理解が誤っていたようで修正
・「USE_INVISIBLE_INDEXES」ヒント句を利用することで「可視索引+不可視索引の全てのインデックスがオプティマイザに利用」される、らしい。

具体的にはIDX1、IDX2など複数の不可視索引が存在している場合に、オプティマイザによってIDX2の方がコスト的に有利と判断された場合、IDX1を指定するだけではIDX2を利用する実行計画となるようだ。
従ってヒント句は下記のように指定する必要がある。

・不可視索引を含めた全てのインデックスを有効化
（可視・不可視の両方からオプティマイザが実行計画を検討する）

 /*+ USE_INVISIBLE_INDEXES */

・特定の不可視索引を指定する場合
（利用させないものも明示的に指定する必要がある）
※TEST_IDX1を明示的に利用させる場合

/*+ USE_INVISIBLE_INDEXES INDEX(TEST_TAB TEST_IDX2) NO_INDEX(TEST_TAB TEST_IDX1) */