35章 索引
それでは今回は索引について学んでいきましょう。
索引というオブジェクトはSQL文の処理速度を向上させることが目的のオブジェクトです。
今までの表やビューとは全く使用方法が異なります。
索引は本の索引と考え方は同じです。英語の辞書で単語を調べるとき
索引を使用して、探している単語が何ページにあるか調べますね。
Oracleデータベースも同様でレコードを探す時に索引があれば、
索引を使用したほうが早く目的のデータにたどりつくことができます。
では辞書に索引がなかった場合、どうやって目的の単語を調べますか？
索引がない場合は、1ページ目から順に探して行く人もいると思います。
Oracleの場合は、索引がない場合、条件に一致したレコードが1件の場合でも、
その表の1行目から最後の行まで順に探していきます。
索引はこのようなSQLの速度を向上することが目的のオブジェクトです。
しかし、むやみに作成しても、性能向上するわけではありません。
[構文] 索引の作成
CREATE INDEX 索引名 ON 表名(列名);
では索引を作成してみましょう。
SQL> create index emp_dept_id_idx on employees(dept_id);
Index created.
作成できました。では次にemployees表のemp_id列にも索引を作成してみましょう。
SQL> create index emp_emp_id_idx on employees(emp_id);
create index emp_emp_id_idx on employees(emp_id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
今度はエラーになりました。原因はemp_idには主キー制約が定義されています。
主キー制約を定義すると暗黙的に索引が作成されます。
索引は1つの列に対して1つのみとなりますので、エラーになったというわけです。
主キー制約だけでなく一意制約に関しても索引が作成されますのでご注意ください。
今回の例ではレコード数は20件程度なため、あまり索引による効果は得られません。
これが数百万レコード、数千万レコードとなると速度も大分変わってきます。
索引を作成する必要性について
索引を作成すれば、早くなるなら全ての列に索引を作成すればいいじゃん！
と思いますが、これは違います。
索引は表が更新(DML文)されれば、索引も更新されます。
表が頻繁に更新されるようなシステムの場合は、それだけ索引を更新するコストも
必要となります。そのため、不要な列に対し、索引を作成するとかえって
データベースのパフォーマンスを低下させる恐れもあります。
以下に索引を作成したほうがよい場合について記載しています。
索引作成の指針
・レコード数が多い表
・頻繁にWHERE句の比較条件で使用されている選択率の低い列
・頻繁に結合が行われている列
こちらも索引を作成した方が、速度が向上する可能性があります。
上記が索引を作成する指針となるものとなります。
また興味がある人はチューニングのコースでこちらは詳細を確認していきましょう。
以上が索引作成方法となります。最後に索引の削除方法です。
索引の削除方法は以下の構文となります。
[構文] 索引の削除
DROP INDEX 索引名;
索引を削除しても、表や制約などに影響はありません。
上記構文で索引を削除することができます。