Oracle｜インデックスのまとめ

Oracleのインデックスについてまとめてみました。

1. インデックスが使用される条件

全データの数％のデータにアクセスする場合、インデックスが使用される。

該当データが多いと、インデックスを作成していてもフルスキャンになることがある。
何故かというと、オプティマイザは、データ（インデックス）にアクセスする回数と１回の平均アクセス時間により、インデックス or フルスキャンを行うかを判断するため。

インデックススキャンはランダムアクセス、フルスキャンはシーケンシャルアクセスが行われ、またストレージ性能によっても、インデックススキャンを使用するかフルスキャンを行うかの閾値が異なる。

1. 一意性が高いインデックス（アクセスの高速化）
2. 使用頻度の高い列を先頭（インデックスの共有化）
3. 選択率の低い列を先頭（データの絞り込みの高速化）
4. 非ユニーク索引にはキー圧縮（インデックスデータの圧縮）

Bツリーでは、テーブルに複数のインデックスが作成されていても、最適な１つのインデックスのみ使用される。

そのため、where句で複数条件を指定している場合は、できるだけ全ての列で複数列インデックスを作成する。
＊一意性が高くない列に設定しても効果は無い。

インデックスを多く作ると、インデックスの更新に時間がかかるため、使用頻度の高い列をインデックスの先頭にすることで、多くのSQLで使用できるインデックスになる。

9iからはスキップ・スキャン機能があり、指定されていない先頭列をスキップしてインデックスを適用できるが効果が薄い。

複数列インデックスの絞り込みは、先頭列から行われるため選択率が低い（ユニーク性が高い）列を先頭にすることで、早く絞り込みを行うことができる。

重複が多いデータの場合、キー圧縮することでインデックスのデータ自体を圧縮して小さくすることができる。
重複データが多い非一意インデックスの場合は、特に圧縮効果が高くなる。

インデックスを効果的に用いるには、断片化、未使用インデックスを定期的に監視しメンテナンスを行う。

インデックスはBツリーのレベル数（ルートからリーフブロックまでの階層）が高くなるとアクセス効率が低下します。

また、データの削除が多く行われるとブロックの使用効率が低下して、Bツリーのレベル数が高くなります。
その場合、インデックスを再構築（REBUILD）する必要があります。

アクセス効率が悪いかは、INDEX_STATSビューの次の値から確認できます。
・HEIGHT（Bツリーの高さ）
・DEL_LF_ROW/LF_ROWS（削除されたエントリーの割合）

HEIGHT が4以上かつ DEL_LF_ROW/LF_ROWS が 0.2を超える場合、効率が悪いため再構築を行うか検討する。

次の1、2のSQLを実行することで、インデックスのアクセス効率を確認できます。

1. 分析対象オブジェクトの構造を検証

ANALYZE INDEX インデックス名 VALIDATE STRUCTURE;

1	ANALYZE INDEX インデックス名 VALIDATE STRUCTURE;

2. アクセス効率を確認

SELECT name, height, lf_rows, del_lf_rows FROM index_stats WHERE name = 'インデックス名';

1	SELECT name, height, lf_rows, del_lf_rows FROM index_stats WHERE name = 'インデックス名';

また、データ件数が多いため階層が深い場合は、インデックスのパーティション化を検討します。

インデックス更新のオーバーヘッドがあるため、未使用のインデックスは削除を検討します。

未使用かどうかは、調査したいインデックスにMONITORING USAGEを指定することで判定することができます。

MONITORING USAGEの設定後に、そのインデックスが使用されたかは、v$object_usageで確認します。

1. モニタリングするインデックスを指定

ALTER INDEX インデックス名 MONITORING USAGE;

1	ALTER INDEX インデックス名 MONITORING USAGE;

2. インデックスが使用されたか確認

SELECT index_name, monitoring, used FROM v$object_usage WHERE index_name= 'インデックス名'

1	SELECT index_name, monitoring, used FROM v$object_usage WHERE index_name= 'インデックス名'

monitoring
値が “YES”の場合は、現在監視中であることを示します。

used
値が”NO”の場合は、対象のインデックスが使用されなかったことを示します。