Oracle 11g 不可視索引に関する検証 その2

<Oracle 11g 不可視索引に関する検証 その2>
ペンネーム: グリーンペペ

前回11g新機能として紹介した”不可視索引”ですが8iからも似たような機能が
あります。”Virtual Index”です。

■”Virtual Index”とは?
“Virtual Indexes”は実際には索引を作成することなく、索引によるSQL文の実
行計画への影響をテストすることができます。
Oracle Enterprise Manager(OEM)の1機能であるVirtual Index Wizardを実現
するために8iから実装されている機能です。
但しOEMの機能を使用せずに”Virtual Indexes”機能を使用するには隠しパラメ
タを変更する必要があり、Oracle社よりサポートされていない使用方法になり
ます。

■”Virtual Index”と”不可視索引”の違い
▽Virtual Index機能の特徴
– 8iからの機能
– 索引の実体はない(セグメントはない)
– 初期化パラメタ_use_nosegment_indexes=trueに設定することで機能を使用
できる
– 実行計画は索引を使用するが、セグメントがないので実際にはSQL文は速く
ならない
– コストベースオプティマイザ(CBO)使用時のみ索引使用可能

▽不可視索引機能の特徴
– 11g新機能
– 索引の実体はある(セグメントはある)
– 初期化パラメタoptimizer_use_invisible_indexes=trueに設定することで機
能を使用できる
– 実行計画は変化し、SQL文も速くなる

■検証環境
Red Hat Enterprise Linux ES release 2.1 (Panama)
Oracle8i Enterprise Edition Release 8.1.7.0.1 – Production

■検証
▽Virtual Indexの作成
Virtual Indexを作成するにはcreate index文にnosegment句を付加するだけです。

SQL> create index ix_ename2 on emp2(ename) nosegment;

Index created.

▽索引の実体を確認してみる
索引はdba_objectsで存在確認ができますが、dba_segmentsでは確認できません。
SQL> select OBJECT_NAME,OBJECT_TYPE from user_objects
where object_name=’IX_ENAME2′;

OBJECT_NAME OBJECT_TYPE
——————– ——————————————————
IX_ENAME2 INDEX

SQL> select SEGMENT_NAME,SEGMENT_TYPE from user_segments
where SEGMENT_NAME=’IX_ENAME2′;

no rows selected
[/sql]
▽Virtual Indexを使ってみる1
Virtual Indexを作成したenameカラムをキーとして検索を実行してみます。

SQL> set autotrace on
SQL> select * from emp2 where ename='tkishimo';

no rows selected

Elapsed: 00:00:00.70

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP2'

Statistics
----------------------------------------------------------
          5  db block gets
       1357  consistent gets
       1355  physical reads

あれ?まだ全件検索のままです。
そうだ!Virtual Indexを使用するには初期化パラメタ_use_nosegment_indexes=trueを
設定する必要があるのでした。

▽Virtual Indexを使ってみる2(初期化パラメタ_use_nosegment_indexes=trueを設定)

SQL> alter session set "_use_nosegment_indexes"=true;
SQL> select * from emp2 where ename='tkishimo';

経過: 00:00:00.70

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP2'

統計
----------------------------------------------------------
          5  db block gets
       1355  consistent gets
       1355  physical reads

あれれ?まだ全件検索のままです。
そうだ!Virtual Indexを使用するにはCBO使用時のみでした。

▽Virtual Indexを使ってみる3(CBO使用設定)

SQL> show parameter optimizer_mode
NAME                                 TYPE                  VALUE
------------------------------------ --------------------- ------------------
optimizer_mode                       string                CHOOSE

optimizer_modeがCHOOSEに設定されていますので統計情報が取得されていなけ
ればRBOで動作します。ヒント句を付加してCBOで動作するようにSQL文を変更
してみます。

SQL> select /*+ first_rows */ * from emp2 where ename='tkishimo';

経過: 00:00:00.73

実行計画
----------------------------------------------------------
 0    SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1107 Bytes=96309)
 1  0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP2' (Cost=2 Card=1107 Bytes=96309)
 2  1     INDEX (RANGE SCAN) OF 'IX_ENAME2' (NON-UNIQUE) (Cost=1 Card=1107)

統計
----------------------------------------------------------
          5  db block gets
       1355  consistent gets
       1355  physical reads

実行計画からVirtual Indexが使用されたことが確認できました。
しかしながら、経過時間を確認すると全く速度向上されていません。
統計を確認すると使用前後でphysical readsが1355回と全く改善されていません。
実体(セグメント)がないのだから、当然なのですが。

■まとめ
Virtual Indexを使用すれば11g以前のヴァージョンでも新規作成索引の効果を
測定することができます。但し実行計画の確認のみで、速度向上の測定までは
できません。11g新機能の不可視索引であれば速度向上の測定もできます。
一度お試し下さい。

雨まじりの茅ヶ崎にて