INDEXの効くタイミング

Question

現在INDEXを使用したらどれほど処理が速くなるのかを検証しています。
そこで何点か質問があります。
・UPDATE、MERGE、INSERTをする際もINDEXは効いているのでしょうか。
・UPDATE、MERGE、INSERTの際も処理速度は速くなるのでしょうか。
※自分が検証した際はコストがINDEX作成前よりもかかり処理時間が長くなるという結果が出ました。
上記につきましても何かしら原因があるとは思います。
・INDEXを作成した後処理をコストを確認しても特にINDEX作成前と変化はありませんでしたが、
ヒント句を使用して以下のようにするとINDEXが効きます。なぜですか？
例：INDEX(SAMPLE IX_0001)

上記の3点の質問があります。
例等がなくわかりにくい文章になっているかもしれません。
回答お願いいたします。

Answer 1

1番目2番目ですが、インデックスを作成した場合、INSERTやUPDATEではインデックスも書き換える必要が出てくるので、一般にこれらの作業は時間がかかるようになります。

Answer 2

3番目ですが、INDEX が効くのは where 条件として適切に利用できる場合です。
また、もとのレコード数が多数である場合に、index による絞り込み件数が少ないと、かえって遅くなることがあります。

※インデックスを経由して検索する場合、まずインデックスを読んで、そこから得られたレコード番号のレコードを順次読む、というランダムアクセスになるため、全件読み取るシーケンシャルアクセスの方が（捨てる分を加味しても）早くなる場合がある

経験則的には、インデックスによる絞り込み結果が全件中の10% 以下になるようであれば高速に処理できるが、それ以上だとインデックスを読むことによるオーバーヘッドが無視できなくなってきます。

Answer 3

インデックスがある時とない時で、
Oracle10g以降でしたら、きちんと統計情報を取得 した上で、実行計画を取得 するとインデックスが使われているかわかります。
それぞれ処理時間を実測することでパフォーマンスの違いがわかります。
テーブルに複数のインデックスがある時で使って欲しいインデックス以外が使われていない時はオプティマイザ・ヒントで使ってほしいインデックスを指定します。

パフォーマンス・チューニング案件でSELECTは速いけどDELETE, INSERT, UPDATE が遅いテーブルがある、って言われて調べたらひとつのテーブルに25個もインデックスが付いていて、インデックスが使われているか調べる方法を教えて、インデックスの整理を提案したこともあります。

大量のデータを扱うなら、PARALLEL実行でパフォーマンスの改善が見込める場合もあります。

Answer 4

・UPDATE、MERGE、INSERTをする際もINDEXは効いているのでしょうか。
・UPDATE、MERGE、INSERTの際も処理速度は速くなるのでしょうか。

INDEXはWHERE条件やorder by やjoin group byなどで使用され、select だからとかupdateだからというのは関係がありません。

大量データを扱ってINSERTするなら、INSERT先のテーブルのINDEXは寧ろ(INDEXを作成するオーバーヘッドが）邪魔です。

直接関係しているかどうかですが、MERGEでのINSERTが値そのものならdual表を使用したものになっているかは気になります。
そもそも、データの状況によっては、MERGEを使用するより、INSERTとUPDATEを使い分ける方が高速な場合だってありますからね。

・INDEXを作成した後処理をコストを確認しても特にINDEX作成前と変化はありませんでしたが、
ヒント句を使用して以下のようにするとINDEXが効きます。なぜですか？

統計情報が正しくなかったり、物理的な記憶域の特性やデータ特性を正しく表現できなかった場合、オプティマイザは正しい判断ができず、最適ではない実行計画を立ててしまいます。
そこへ最適なものを教えるというのがヒント句ですから。

Oracleのオプティマイザとは何か？～ソースコードを書いているエンジニアが語る

Answer 5

Indexは辞書の索引と同じようなもの（値とページ数が書かれているやつ）にすぎません。
なのでIndexは基本的に検索する際にしか利用されません。

1つ目　
UPDATE、MERGE、INSERTをする際に検索が必要でIndexを利用できれば利用されます。

2つ目　
関係するIndexを併せて更新するため遅くなります。
UPDATEのように更新する対象を検索する必要がある場合、検索処理は早くなる可能性があります。

3つ目　
コストベースオプティマイザは計算したコストが最小のものを採用します。
Indexを利用しても対象を絞り込めない、データ量が少なく全件読みだしても問題ないとオプティマイザが判断している（Indexを利用したほうがコストが大きい）と利用されません。

ヒント句はオプティマイザに実行計画を指示するものです。
指定した場合は指示に従って計画を立ててコストを計算し、その中でコストが最小のものを採用します。

Answer 6

とあるデータベースを使っている者ですが、あくまでも経験則で記載させて頂きます。
まずインデックスに関しては、インデックス情報と主キーを紐付けるテーブルと考えるとわかりやすいと思います。
この紐付ける為のテーブルを更新する為、書き込みが増えるので、インデックスが増加すると書き込み速度が遅くなるという事になります。
尚、InsertやDeleteに関しては、他の方も書かれている通り、大量データに対して、Where句で絞り込みが効く物であれば、更新対象のデータを見つける迄の時間が短くなるので、インデックスの書き込み速度との関連で書き込み速度以上の検索スピード向上にインデックスが貢献すれば、スピードは上がると思います。
Insert等では、Where句が無い為、インデックスの書き込み速度分が増加になると思います。