インデックスに関する検証その３

＜インデックスに関する検証その3＞ペンネームつけまい

— インデックス利用の落とし穴
レンジ検索で全件検索よりも性能ダウン —

テーブルTEST01には、EMPNO01とEMPNO02という項目があり、それぞれ同じ値が
格納されている。また、EMPNO01にはインデックスを作成している。格納されて
いるデータは、10000010から10単位で増加させ、10100000までの計1万件が格納
されている。

テーブルTEST01

このテーブルに対して、

1. 5000件目のイコール検索

2. ～5000件目までのレンジ検索

をそれぞれEMPNO01（インデックス項目）及びEMPNO02（インデックスなし）を
SELECT文の条件式に用いた結果を以下に示す。

図中の(※2)、(※3)、(※4)は検索する前後に以下のSQL文を発行し、前後の差
分を求めた結果である。

●I/O回数を求めるSQL文

SELECT SUBSTR(A.NAME, INSTRB(A.NAME, '../', -1) + 1, 20) FILE_NAME
,A.STATUS
,TO_CHAR(B.PHYRDS, '99999999999990') PHYRDS
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE# ;

検索結果で注目していただきたいのが、＜検索1＞の条件式にインデックス
（EMPNO01）を用いたイコール検索である。「インデックスの構造」でルート、
ブランチ、リーフと、3段階の過程を経て目的とするレコードに到達すると説明
をしたが、この結果ではインデックスのブロックに対してのI/Oは2回しか発生
していない。これは、データが1万件という少ない件数なので、2段階で納まっ
ていることを表している。このことは、以下に示すANALYZEコマンドを用いて確
認することができる。

SQL> ANALYZE INDEX TEST01 VALIDATE STRUCTURE ;
└→ テーブル名ではなくインデックス名
索引が分析されました。
SQL> SELECT HEIGHT,         -- ブランチ・ノードの階層の高さ
2         BLOCKS,         -- 全ブロック数
3         LF_ROWS,        -- リーフ行数
4         LF_BLKS,        -- リーフ・ブロック数
5         BR_ROWS,        -- ブランチ行数
6         BR_BLKS         -- ブランチ・ブロック数
7    FROM INDEX_STATS ;
HEIGHT    BLOCKS   LF_ROWS   LF_BLKS   BR_ROWS   BR_BLKS
--------- --------- --------- --------- --------- ---------
2     51200     10000        87        86         1

上記の結果で注意していただきたいのが、全ブロック数（BLOCKS）である。
51200となっているがリーフ・ブロックとブランチ・ブロックの合計は88になっ
ている。これは、CREATE INDEXでインデックスを作成した際、エクステントを
発生させないためにINITIAL 100Mで領域を確保しているために発生した完全な
未使用ブロック及び、インデックスの「領域管理機構上の問題」であり、「パ
フォーマンスの優先」から生じる未使用ブロックが存在している事を表してい
る。

次に注目していただきたいのが、＜検索3＞の条件式にインデックス（EMPNO01）
を用いたレンジ検索である。ここでのインデックスに対するI/Oは45回発生して
いる。これは、1件目の値10000010から5000件目の値10050000までが合計44のリ
ーフ・ブロックに格納されており、これにブランチ・ブロックの数1をたした値
である。このことは、次回で紹介するTREEDUMP機能を用いて確認することがで
きる。

初夏茅ヶ崎にて

～インデックスに関する検証その3～
by つけまい

インデックスに関する検証 その３