Oracleのインデックスについて

Question

Oracleでインデックスを作成するときに以下の方法１と方法２に違いはありますか？

方法１
CREATE INDEX tmp1_index ON tmp1 (col1, col2);

方法２
CREATE INDEX tmp1_index ON tmp1 (col1);
CREATE INDEX tmp1_index ON tmp1 (col2);

また、select文を書くときにインデックスを使用することはあるのでしょうか？

Answer 1

いきなり余談ですが、質問の通りの記述だと、方法2は2個目のSQLが失敗します。1個目のSQL でtmp1_index を作っているので、2個目に「同じ名前で」create しようとするので。

SELECT 時にインデックスを強制する方法として、ヒントというものがあります。オプティマイザに対して指示をするものです。

例えば SELECT の対象列がインデックスに含まれていない場合、インデックスでレコードを決めても、そのレコードを取ってこないと必要な列が得られません。テーブルの列数が多くかつレコード数も多くなると、インデックスによる絞り込み効果より、インデックスをみながらレコードを取ってくる処理の方がコストが大きくなる可能性があります。
※レコード範囲が決まっていればどかんと一回で持ってこれる（例えばカードの一束）のですが、それを1レコードずつ持ってきたら（1枚ずつ抜いて渡す）遅くなるのは自明ですよね

このような場合に、「フルスキャンしろ」という指定をすることも可能です。
ただしこの機能は古い Oracle で、まだオプティマイザの判定が甘かったころに用意されて踏襲されているものなので、中長期的にはきちんと最適化をして、自動で適切なインデックスが使われるように保守していくべきです。

Comments

[退会済みユーザー]

ご回答ありがとうございます。

Answer 2

方法1はcol1とcol2の組み合わせに対してインデックスが作成されます。

方法2ではそれぞれ単独の列に対してインデックスが作成されます。

select/insert/update/delete、いずれにしろインデックスは
利用されます。

方法1のインデックスに対して

select col1, col2
from tmp1
where col2 = 'XXX'

と指定した場合、インデックスは利用されません。
インデックスの順番とWHERE句の順番は意識して
記述していただく方が良いです。

【SQLチューニング】で検索し、インデックスが効くSQL、
効かないSQLについて一度学習していただくと理解が深まると思います。

Comments

[退会済みユーザー]

ご回答ありがとうございます。

Answer 3

Oracle9i以降でCBO(つまりきちんと統計情報が取得されている)なら インデックス・スキップ・スキャン が使えることもあるので、方法１の索引しかなくて WHERE句に COL2 の条件しか記述されていなくても索引が使われる場合もあります。
索引が使われているかどうかは EXPLAIN PLAN を使って実行計画を取得する で調べることができます。

5年以上前だったと思いますが、WHERE句の記述にはまったく関係なく、ひとつの索引には1列しか設定しない、って主義の人(大企業の正社員)にあったこともあります。笑い

Comments

[退会済みユーザー]

ご回答ありがとうございます。