Virtual Index Tweet
個人的にはOracle9iのころも含めて実践では利用した事は無い機能なのだが、Oracle ACEの一人である、Chris Foot氏が、dbazineでVirtual Indexについて書いていたので試してみることにした。
注）仮想索引は、今のところマニュアルに記載されていない隠し機能の一つなので、利用する際は、your own risk! ですよ。
まずは、準備から。dbazine.comの記事でも利用されている、HRスキーマ（サンプルスキーマ）を利用し試してみた。
SYS>
SYS> select count(*) from hr.departments;
COUNT(*)
----------
27
SYS> select count(*) from hr.employees;
COUNT(*)
----------
107
SYS> create table scott.departments as select * from hr.departments;
表が作成されました。
SYS> create table scott.employees as select * from hr.employees;
表が作成されました。
SYS> conn scott/tiger
接続されました。
SCOTT> insert into employees select * from employees;
107行が作成されました。
....中略....
SCOTT> r
1* insert into employees select * from employees
109568行が作成されました。
SCOTT> commit;
コミットが完了しました。
SCOTT>
SCOTT> alter table departments add constraint pk_departments primary key (department_id);
表が変更されました。
SCOTT> exec dbms_stats.gather_schema_stats(ownname=>'SCOTT',cascade=>true);
PL/SQLプロシージャが正常に完了しました。
SCOTT> alter session set optimizer_mode = 'FIRST_ROWS';
セッションが変更されました。
SCOTT> l
1 select
2 employee_id,
3 e.department_id,
4 d.department_name
5 from
6 employees e join departments d
7 on e.department_id = d.department_id
8 where
9* employee_id = 203
SCOTT> /
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2011 Card=1481 Bytes=35544)
1 0 NESTED LOOPS (Cost=2011 Card=1481 Bytes=35544)
2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=514 Card=1494 Bytes=11952)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=16)
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPARTMENTS' (INDEX (UNIQUE)) (Cost=0 Card=1)
SCOTT>
empoyees表のemployee_id列に索引がないので、FULLスキャンになっているようですね。
ということで、準備完了！
● 早速、仮想索引 (virtual index)を作成してみる！ create index文に、 nosegment句を付けるだけ。
ついでに統計情報も取得する。
SCOTT> create index empid_v_idx on employees(employee_id) nosegment;
索引が作成されました。
SCOTT> exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'EMPID_V_IDX');
PL/SQLプロシージャが正常に完了しました。
● 実行計画を確認してみる。
おやおや、変化しないですねぇ。って当然です。Virtual Indexは今のところ隠し機能なので、
_use_nosegment_indexes という隠しパラメータを true にセットしないと機能しません。
SCOTT> set autot trace exp
SCOTT> l
1 select
2 employee_id,
3 e.department_id,
4 d.department_name
5 from
6 employees e join departments d
7 on e.department_id = d.department_id
8 where
9* employee_id = 203
SCOTT> /
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2902 Card=2365 Bytes=56760)
1 0 NESTED LOOPS (Cost=2902 Card=2365 Bytes=56760)
2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=513 Card=2384 Bytes=19072)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=16)
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPARTMENTS' (INDEX (UNIQUE)) (Cost=0 Card=1)
SCOTT>
● _use_nosegment_indexesパラメータをtrueに設定して再チャレンジ。
おお〜〜〜。employee_id列に非ユニーク索引を作成すれば、このクエリは早くなりそうですね。
SCOTT> alter session set "_use_nosegment_indexes" = true;
セッションが変更されました。
SCOTT> l
1 select
2 employee_id,
3 e.department_id,
4 d.department_name
5 from
6 employees e join departments d
7 on e.department_id = d.department_id
8 where
9* employee_id = 203
SCOTT> /
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=273 Card=2365 Bytes=56760)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (TABLE) (Cost=10 Card=88 Bytes=704)
2 1 NESTED LOOPS (Cost=273 Card=2365 Bytes=56760)
3 2 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (TABLE) (Cost=3 Card=27 Bytes=432)
4 2 INDEX (RANGE SCAN) OF 'EMPID_V_IDX' (INDEX) (Cost=1 Card=2384)
SCOTT>
● では、本当の索引を作成したらどうなのでしょうね。
おお〜〜〜。Cost=3042もある〜〜〜。 Virtual Indexの時は、Cost=273なのに〜〜。
だから、隠し機能なんですかね？？？？。
単純な問合せでは、Virtual Indexとの差はあまりなかったのですが。。。
SCOTT> drop index empid_v_idx;
索引が削除されました。
SCOTT> alter session set "_use_nosegment_indexes" = false;
セッションが変更されました。
SCOTT> create index empid_idx on employees(employee_id);
索引が作成されました。
SCOTT> exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'EMPID_IDX');
PL/SQLプロシージャが正常に完了しました。
SCOTT> l
1 select
2 employee_id,
3 e.department_id,
4 d.department_name
5 from
6 employees e join departments d
7 on e.department_id = d.department_id
8 where
9* employee_id = 203
SCOTT> /
実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3042 Card=1657 Bytes=39768)
1 0 NESTED LOOPS (Cost=3042 Card=1657 Bytes=39768)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (TABLE) (Cost=1365 Card=1673 Bytes=13384)
3 2 INDEX (RANGE SCAN) OF 'EMPID_IDX' (INDEX) (Cost=4 Card=1673)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=16)
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPARTMENTS' (INDEX (UNIQUE)) (Cost=0 Card=1)
SCOTT>
● なぜ？ 実行計画に差がでてしまうのだろうか？？？？ （だから隠し機能のままなのか？？？） 疑問。。。
推測なので、間違っているかもしれないという前提ですが、以下のログを見てもらいたい。
user_objectsには索引として存在しているが、 user_indexesには存在しない。
user_indexesに存在しないということは、dbms_statsパッケージでアナライズしたVirtual Indexの統計情報はどこに格納されるのか？？？
それがポイントになりそうな気がします。統計情報の格納場所が無い ＝ 統計情報が無い ＝ 統計情報の欠落 ＝ コストベース・オプティマイザは、統計情報のデフォルト値を利用して実行計画を立てる！
ということになるのではないか？ と思った次第。従って、実体のある索引とはかけ離れたCostが算出されてしまう。
これは私個人の推測に過ぎません。なにせ、隠し機能なので、マニュアルにも解説は無いので・・・。このようなことを踏まえた上で、開発環境で、試してみる程度なら楽しいかもしれませんね。。（趣味の世界の話になっちゃいますけどね。。笑）
今回は、Oracle10g R1を利用した結果なので、Oracle10g R2でも試してみましょうかね。気が向いた時にでも。
SCOTT> l
1 select
2 object_name
3 ,object_type
4 from
5 user_objects
6 where
7* object_type = 'INDEX' and object_name = 'EMPID_V_IDX'
SCOTT> /
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
EMPID_V_IDX INDEX
SCOTT> l
1 select
2 index_name
3 ,table_name
4 ,index_type
5 ,uniqueness
6 ,initial_extent
7 ,last_analyzed
8 ,status
9 from
10 user_indexes
11 where
12* index_name = 'EMPID_V_IDX'
SCOTT> /
レコードが選択されませんでした。
SCOTT>
| 固定リンク | 0
コメント
追伸
>おお〜〜〜。Cost=3042もある〜〜〜。 Virtual Indexの時は、Cost=273なのに〜〜。
プラットフォームの異なるOracle10g R2でも全く同じ結果なのでR1特有ということは全くなさそう。
投稿: discus | 2007年7月14日 (土) 21時22分