3-6 SQLパフォーマンスチューニング　－最適なSQL文の書き方－

正しい索引の使用方法

INDEXの作成と管理

9 April, 1998 Updated　　
PCDN OracleWG S.Yamazaki　　

　さて、「最適なSQL文の書き方」で述べたように、索引を使用するかしないかにより、レスポンスに影響することはおわかりいただけたと思います。索引が使用されるかどうか．．．これは、どのような検索条件をもってデータを抽出するかによります。これを解決するためには、その業務の性質なりを把握しなければ、明確な答えがでるとも思えません。ここでは、より明確な答えを導くための指針を示せればと思います。
　また、Oracle7.3以降からの索引に関する新機能も併せて紹介したいと思います。機会があればお試しください。

索引とは

　索引(INDEX)とは、実表(TABLE)に格納されているデータを効率よくアクセスするために、実表とは別な領域を使用して格納されるデータの集合です。
　これは、辞書などにもある「目次」「INDEX」などと同じように考えることができるでしょう。五十音順の目次、機能別の目次とかいろいろ種類もありますが、それらの右には、かならずページ番号がふされていますよね、これがOracleではROWIDにあたると考えられます。

索引の種類

Bツリー索引
　この索引を付ける目安は、データ分布が高い列に付けるのが一般的です。
　ただし、複数列に指定した複合索引作成において注意しなければいけないことは、その索引列を同じ順で検索条件に指定しないと、オプティマイザが使用してくれないということでしょう。

例えば、
　頻繁に条件指定するA,B,C列のあるテーブルがあったとします。そして、A,B,Cの順で索引を作成した場合、
　あるアプリでは、WHERE A=xx AND B=xx AND C=xx,
　他のアプリでは、WHERE B=xx AND C=xx,
　またあるアプリでは、WHERE A=xx AND C=xx,　だとしたら、この索引は最初の問合わせしか使用されないことになり、全て効率よく検索させるためには３種類の索引が必要と言うことになってしまいます。
　この場合、検索系が主体であれば、パフォーマンスが向上しても、挿入や更新も頻繁に行われる場合は逆にオーバーヘッドもかかることになるので、索引している場合は注意が必要です。

以下の図は、Bツリー索引の構造を図式化したもです。(ビットマップ索引とは異なります)

実際に、索引の構造がどのようになっているか(上の図をイメージしたい場合)は、以下のSQL文である程度判断できます。

SELECT index_name, index_type,distinct_keys,blevel,leaf_blocks,
       avg_leaf_blocks_per_key,clustering_factor
FROM user_indexes

　それぞれの列の意味について、統計情報の「索引の統計」をご覧いただくか、「Oracle8 Server リファレンス・マニュアル　リリース8」を参照してください。

　また、上記の図中にあるように、一度削除や更新によって空き領域ができた場合は、ブロック内の索引値が全て削除されないかぎり、再利用されない可能性が高く、無駄な領域が残ることにもなります。このようなフラグメンテーション(断片化)が起きてるかどうかをある程度判断できそうなのが、INDEX_STATS表です。ぜひ、一度お試しください。
(ANALYZE INDEX index_name VALIDATE STRUCTURE; をやったあとのみ有効。)

ビットマップ索引(R7.3.2以降)
　Bツリー索引とは逆に、ビットマップ索引は、データの分布率が低い列(例えば、性別とか年度など)に作成すると効果が得られます。
　この索引の格納方法は、上述のBツリー索引とは違い以下のように格納されています。

実際の表データとビットマップ索引の格納


氏名 	性別 年齢
SMITH 男 32
ALLEN 女 30
WARD 男 28
JONES 男 34
MARTIN 女 33
BLAKE 男 31
CLARK 男 30


氏名(ROWID) 男 女 	28 29 30 31 32 33 34
SMITH 	1 0 	0 	0 0 0 1 0 0
ALLEN 	0 1 	0 0 1 0 0 0 0
WARD 	1 0 	1 0 0 0 0 0 0
JONES 	1 0 	0 0 0 0 0 0 1
MARTIN 	0 1 	0 0 0 0 0 1 0
BLAKE 	1 0 	0 0 0 1 0 0 0
CLARK 	1 0 	0 0 1 0 0 0 0

　上の場合、「性別」は２つの値、「年齢」には７つの値があり、それぞれの行に対応したビットストリームが作成されます。
　次のSQL文を考えて見ます。
　SELECT 氏名 FROM 性別='女' AND 年齢>30

　この場合の検索は、

氏名(ROWID) 男女

ALLEN 0 1

MARTIN 0 1

AND

氏名(ROWID) 28 29 30 31 32 33 34

SMITH 0 0 0 0 1 0 0

JONES 0 0 0 0 0 0 1

MARTIN 0 0 0 0 0 1 0

BLAKE 0 0 0 1 0 0 0

=

氏名性別年齢

MARTIN 女 33

というように抽出されます。

逆キー索引(R8.0.x以降)

　この索引は、順序を保存するとき列のバイトを逆に格納するもので、リーフブロック全体に分散され格納されることになります。偏ったリーフブロックに集中するような業務ではパフォーマンス低下を防ぐこともできます。
　ただし、キー値が連続していないので、範囲検索はできないという欠点もありますので、状況に応じて選択された方がよいでしょう。

　格納されるイメージとして例を挙げてみましょう。以下のようなデータは普通、[A]の用に格納されています、バイトに直すと[B]となり、バイトを逆にして順序が保たれた[C]が逆キー索引となります。(例では、簡単なアルファベットで掲げてますが、数値や日付値も同様です。)
[A]
ALLEN

BLAKE

CLARK

JONES

MARTIN

SMITH

WARD

= [B]
41 4C 4C 45 4E

42 4C 41 4B 45

43 4C 41 52 4B

4A 51 4E 45 53

4D 41 52 54 49 4E

53 4D 49 54 48

57 41 52 44

= [C]
WARD 44 52 41 57

BLAKE 45 4B 41 4C 42

SMITH 48 54 49 4D 53

CLARK 4B 52 41 4C 43

ALLEN 4E 45 4C 4C 41

MARTIN 4E 49 54 52 41 4D

JONES 53 45 4E 51 4A

　逆キー索引を作成する場合は、以下のようなSQL文を発行してください。
　CREATE INDEX index_name ON table_name (columns_name, ...) REVERSE;

　また、逆キー索引を通常の索引に再構築し直すには、以下のようなSQL文を発行してください。
　ALTER INDEX index_name REBUILD NOREVERSE;

正しい索引の使用方法

　索引がどのように管理されているかはおわかりいただけたと思いますが、正しい索引の使用方法は業務内容に応じて付けなければいけないでしょう。
　検索業務が主である場合は、索引を条件指定される列または複数列に索引を付けると、レスポンスは向上します。逆に、更新系(挿入や削除含む)の業務では、逆に索引を付けすぎるとレスポンスに影響してしまいます。
　この辺の検索系と更新系のトレードオフということになり、一概に良いか悪いかの判断は、経験するしかありません。

　さらに、Bツリー索引とビットマップ索引では、ロックの方法も違います。一般的にビットマップ索引はBツリー索引より、複数行(ブロック)をロックする確率が高いので、このあたりも注意する必要があります。
　容量、データ値の分布率、テーブル構造、頻繁に使用するSQL構文．．．など、あらゆる観点から判断して索引を付けることが最良の方法かと思います。

Oracleのオプティマイザ
ヒントを使用する
実行計画の分析
統計情報

Oracle WorkGroup
int21 ホームページ | PCDN ホームページ