Oracle 11g 不可視索引に関する検証 その２
＜Oracle 11g 不可視索引に関する検証 その２＞
ペンネーム： グリーンペペ
前回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を使ってみる１
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を使ってみる２(初期化パラメタ_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を使ってみる３(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新機能の不可視索引であれば速度向上の測定もできます。
一度お試し下さい。
雨まじりの茅ヶ崎にて