Full Scanを速くしちゃう　その６

＜Full Scanを速くしちゃう　その６＞
ペンネーム：グリーンペペ

Full Scanを速くするテクニックとしてIndex Fast Full Scan(以下IFFS)を取
り上げる。IFFSは索引ブロックを全てスキャンする。その中からリーフブロッ
クに格納されている索引キーの列の値を取り出すことでテーブルFull Scanを
せずに列データを取り出すことができる。
但しIFFSを使用するには下記条件を満たしていなければならない。

1.select文で参照している列が全てインデックスキー項目として含まれている
こと。
2.コストベースオプティマイザが実行計画を立てていること。もしくはヒント
句/*+ inedx_ffs */を使用していること。
3.インデックスキー列内の少なくとも一つの列にnot null制約が存在すること。

テーブルに対するアクセスが必要ないということは、通常、表よりもインデッ
クスの方がセグメントサイズが小さいため、読み込むブロック数が少なく、高
速に処理が完了する。
また、IFFS時のDiskアクセスはdb_file_multiblock_read_countに指定したブ
ロック数によりマルチブロックで実行される。

早速検証してみよう。

◆環境
HP-UX hp11i B.11.11
Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production

◆検証１

SQL> select /*+ rule */ count(s_i_id) from tpc.stock;

Elapsed: 00:00:51.76

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'STOCK'

SQL> select /*+ index_ffs(stock) */ count(s_i_id) from tpc.stock;

Elapsed: 00:00:04.79

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=449 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'STOCK_I1' (UNIQUE) (Cost=449 Card=1)

効果は歴然である。10倍以上のレスポンスの差が実証された。
ではIFFSの効果を積極的に使用したケースで検証したい。

ハイウォーターマーク(以下HWM)がdelete処理などで大きくなったままの表や
行移行が多く発生している表をFull Scanする場合は、無駄なセグメントブロ
ックへアクセスする必要がある。
そして厄介なのはこれらの問題が発生している表を再構成するにはexp/impや
alter table moveコマンドにて対応する必要があることだ。何れの解決方法に
おいても表に対する排他ロックが必要なため業務を止める必要がある。24*365
日対応が求められるサイトなどでこれら問題が多く発生している場合、パフォ
ーマンス上クリティカルな問題に発展する場合も少なくない。(10gからは排他
ロックを必要としないshrinkコマンドが実装された。詳細は

検証10g新機能　その１

このようなケースの場合、インデックスを使用することでHWMが大きくなって
しまった表へアクセスすることなく、Full Scanの処理が行える。
HWMが大きくなった表の複製としてインデックスを作成し、IFFS処理として実
行すればよい。
この場合、例えインデックスのHWMが大きくなってしまっていても、8i以上の
Enterprise Editionであればalter index rebuild onlineコマンドが使用でき
る。このコマンドであれば業務を止めずにインデックスの再構築を実施できる。

◆検証２

SQL> select count(*) from emp;

  COUNT(*)
----------
         1

Elapsed: 00:00:16.76

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6302 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=6302 Card=160000)

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

SQL> select table_name,blocks from tabs where table_name='EMP';

TABLE_NAME  　　BLOCKS
----------- ----------
EMP	　  　 　65510

emp表には1件しかデータがないにも関わらず、HWMは65510ブロック目に位置し、
処理に約16秒を要している。

SQL> create index ix_emp on emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);

SQL> select count(*) from emp;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.23

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'IX_EMP' (NON-UNIQUE) (Cost=4 Card=1)

効果はレスポンス時間で比較すると72倍以上アップした。

◆まとめ
大きくなってしまった表のメンテナンスが業務が止められないなどの理由によ
り不可能な場合で表のFull Scanをしないといけない場合はIndex Fast Full
Scanの活用は有効なチューニングである。
しかし、表に新たなインデックスを作成した場合、更新処理ではインデックス
の更新が必要になってくるため更新処理のレスポンスが落ちてしまう。注意し
て是非活用して頂きたい。

インドより暑い？茅ヶ崎にて

Full Scanを速くしちゃう その６