インデックスを使うとなぜ速くなるのかwww.doppo1.netインデックスを使うと何故早くなるのか本ページではORACLEにおいてインデックスを使うと何故クエリが早くなるのか、またはなぜ速くならないのかを説明します。本ページでは索引構成表やビットマップインデックス等の特殊なデータ構造を持つオブジェクトは考慮しておらず一般的なテーブルとインデックスを想定しています。前提知識インデックスを使うとなぜ速くなるのかを理解するためには以下の前提を理解している必要があります。・ディスクアクセスはCPUアクセスと比較すると非常に遅い一般的にはCPU処理時間がナノ秒単位であるのに対してディスクのアクセス時間はミリ秒単位であり、ディスクはCPUに比べて100万倍程度処理が遅いとされています。したがって、クエリの処理時間はディスクアクセスが最も少ない実行計画が最も高速となる可能性が高いと考えられます。・I/Oの単位はブロックであるORACLEのI/Oの最小単位はブロックなので必要なデータサイズが1bitだったとしてもブロックサイズが8Kであれば8KのI/Oが発生します。・テーブルデータはソートされていないORACLEのテーブルに格納されているデータはソートされていないためどこにどのようなデータが入っているかは分かりません。・インデックスはBツリー構造で構築されるORACLEのインデックスはインデックスキーによるBツリー構造になっておりデータがソートされた状態で格納されます。インデックス有無による検索処理の差異ここでは以下のテーブルと検索条件を仮定します。検索条件は1000件中1件だけヒットする条件であるインデックスがない場合ORACLEのテーブルデータはソートされていないため検索条件に合致するデータを調べるためには100ブロック(800K)のI/Oが発生します(テーブルフルスキャン)。インデックスがないテーブルを検索する場合検索条件の内容によらず全てのブロックを読み込まなければ正しい検索結果が戻せません。上記テーブルに対して検索条件列に対してインデックスを作成したところ以下のようなBツリー構造のインデックスが作成されたとします。線がつながっているブロックに対してはブロック内にポインタ情報(rowid)が記録されているためダイレクトにそのブロックを読みにいくことができます。検索条件が「値が500のデータ」だったとすると、1→3→6→12と計4ブロック(32K)のI/Oで目的のデータが得られます。この場合はインデックスがない場合と比較してI/O量が減少するためパフォーマンスが向上します。検索条件が「値が1~500のデータ」(選択率50%)という検索条件だった場合インデックススキャンのほうがI/O量が減少するとしても大抵の場合テーブルフルスキャンのほうが早くなります。これはインデックスによるアクセスの場合は必ず1ブロック単位でI/Oが発生するのに対し、テーブルフルスキャンの場合は複数ブロックを一度に読み込むことができるためです。この一度に読み込むブロック数は初期化パラメータ「db_file_multiblock_read_count」の値で決まります。同じI/O量であればインデックススキャンよりもテーブルフルスキャンのほうが圧倒的に速いですが、インデックススキャンよりもテーブルフルスキャンのほうが早くなる割合はデータ構造やディスク性能等に大きく影響を受けるため一概には言えませんが20%程度を目安にするとよいと思われます。非効率なインデックス検索条件の選択率以外に、以下のような場合はインデックスが遅くなる場合があります。インデックスキーが多い、または長いブロックサイズは一定でありブロック内に格納できるデータには限りがあるため、インデックスのキー列が多い場合やキー列が非常に長いような場合インデックスを構成するブロック数が多くなり検索が遅くなります。char型は固定長のため、定義サイズが大きく実際に格納されているデータが短い場合は無駄な空白領域でブロックを占有してしまうので可能であればchar型ではなくvarchar2型の使用を検討します。インデックスのクラスタ化係数が高いクラスタ化係数とはインデックスのリーフブロックに記録されているテーブルブロックへのポインタの一意な数がどの程度まとまっているかを表した値で統計情報を取得するとDBA_INDEXESのCLUSTERING_FACTOR列で確認することができます。インデックスキーが同じデータが複数のブロックに分散されて格納されている場合はクラスタ係数が高く(悪く)なり、クラスタ化係数の最も望ましい状態は全リーフブロック数の値で最も非効率な状態ではテーブルの全行数の値です。1テーブルに対して複数インデックスが定義されている場合全てのクラスタ化係数を最適化することは難しいですが、頻繁に使用されているインデックスやパフォーマンスが悪いクエリで使用されているインデックスのクラスタ化係数が悪い場合以下を検討します。<キー列>等でデータをソートして作り直す)インデックスの断片化インデックスはキー列が更新されるたびに基本的には断片化が進みパフォーマンスが劣化します。rebuild)をすることで解消されます。その他の使用用途インデックスを利用することで単純な検索以外にも以下のようなケースで高速化することができます。・インデックスファストフルスキャンテーブルのブロック数と比較するとインデックスのブロック数は少なくなる場合が多いため、インデックスブロックの読み込みのみで対処できる処理はテーブルにはアクセスせず、インデックスブロックのみフルスキャンすることで処理を高速化できるものがあります。通常のインデックススキャンとは異なり、テーブルフルスキャン同様に複数ブロックを同時に読み込む処理を全インデックスブロックに対して実施する処理をインデックスファストフルキャンと呼びます。インデックスファストフルキャンを利用するには条件がありますが利用できる代表的な処理には以下のようなものがあります。NULLやプライマリキー制約が付いた列のインデックスである必要があります。・max値、min値の取得インデックスのブロックはソートされているためキー値の最小値、最大値はインデックスブロックをたどれば容易に値を取得することができます。本サイトはリンクフリーです。本サイトの情報により発生した問題等については一切責任を負えません。管理人への連絡はftpsaba0123@hotmail.comまでどうぞ。