Oracleデータベースにおいて、**索引（インデックス）**は、データベースのパフォーマンスを劇的に改善するための重要な仕組みです。適切に利用されれば、クエリの速度向上に大きな効果をもたらしますが、逆に不適切な索引の作成や運用は、パフォーマンス低下を引き起こす可能性があります。この記事では、索引の基本概念とその効果的な使用方法に加え、適切でない索引設計による問題点についても解説します。
索引とは？
索引は、テーブル内のデータの物理的な場所を効率的に見つけるための「目次」のような役割を果たします。索引があることで、Oracleデータベースはすべてのデータを順番に調べるフルテーブルスキャンを行うことなく、特定のデータを迅速に見つけることができます。
たとえば、EMPLOYEESテーブルにある従業員の姓（LAST_NAME列）を検索する場合、索引がないとすべての行を1つずつチェックする必要がありますが、LAST_NAME列に索引を作成しておけば、検索が効率化されます。
索引の作成方法
索引を作成するには、CREATE INDEX文を使用します。例えば、EMPLOYEESテーブルのLAST_NAME列に索引を作成するには次のようにします。CREATE INDEX <索引名> ON <テーブル名>(<索引を作成する列名>);
索引が適切に作成されていない場合の問題点
索引は強力なツールですが、適切に作成されていない場合、データベースのパフォーマンスに悪影響を与えることがあります。以下は、その具体例です。
- 不要な索引の作成
頻繁に使用されない列や小さなテーブルに対して索引を作成すると、クエリのパフォーマンスにはあまり寄与しません。逆に、索引の作成や更新にかかるオーバーヘッドが発生し、パフォーマンスが低下することもあります。 - 重複した索引の作成
同じ列に対して複数の索引を作成することは、ディスク領域の無駄遣いであり、不要な処理が増えてしまいます。既存の索引を確認し、重複するものがないか注意することが重要です。 - 適切でない列に索引を作成
索引は、主にWHERE句やJOIN句で頻繁に使用される列に作成することが理想です。更新や挿入の頻度が高い列や、ほとんど検索に使われない列に索引を作成すると、逆効果になることがあります。 - 低選択性の列に索引を作成
選択性が低い（多くの行が同じ値を持つ）列にB-tree索引を作成すると、索引が十分に機能しません。このような列では、ビットマップ索引を検討するか、そもそも索引を作成しない方が良い場合もあります。
適切な索引の運用ポイント
索引の効果を最大限に引き出すためには、いくつかの重要なポイントを押さえておく必要があります。
- 頻繁に検索に使用される列を選ぶ
主にWHERE句で使用される列や、JOINに使われる列に対して索引を作成するのがベストです。これにより、クエリが高速化されます。 - 挿入・更新が多い列への注意
索引がある列でデータの挿入や更新が行われるたびに、索引自体も更新されます。これがオーバーヘッドとなり、書き込み処理のパフォーマンスを低下させることがあります。 - 統計情報を定期的に更新する
Oracleのクエリオプティマイザが最適な実行計画を選択するためには、テーブルや索引の統計情報が最新であることが重要です。DBMS_STATSパッケージを使って統計情報を更新しましょう。
【統計情報を取得するコマンド例】EXEC DBMS_STATS.GATHER_TABLE_STATS('<スキーマ名>', '<テーブル名>');
索引は例えば「従業員番号」や「携帯電話番号」等のその他の行と値が重複しない、一意な値が格納されている列に作成するのが望ましい。
以下のように一意な値が格納されている列に作成するのが良い。
SQL> show user
ユーザーは"SYS"です。
SQL> select empno,ename,job,deptno from user1.emp;
EMPNO ENAME JOB DEPTNO
---------- ---------- ---------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
7654 MARTIN SALESMAN 30
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7839 KING PRESIDENT 10
7844 TURNER SALESMAN 30
7900 JAMES CLERK 30
7902 FORD ANALYST 20
7934 MILLER CLERK 10
12行が選択されました。
SQL> select owner,index_name,index_type,table_name from dba_indexes where owner='USER1';
レコードが選択されませんでした。 ★索引が作成されていない
SQL> conn user1/user1
接続されました。
SQL> create index index_emp on emp(empno); ★EMPNO列に索引を作成
索引が作成されました。
SQL> conn / as sysdba
接続されました。
SQL> select owner,index_name,index_type,table_name from dba_indexes where owner='USER1';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME
---------- ---------- ---------- ----------
USER1 INDEX_EMP NORMAL EMP ★索引が作成された
SQL>
索引の削除方法
不要な索引やパフォーマンスを悪化させる可能性のある索引は、DROP INDEX文を使って削除することができます。DROP INDEX <索引名>;
まとめ
索引は、Oracleデータベースのクエリ性能を最適化するための重要なツールですが、適切に設計・管理されていないと逆にパフォーマンス低下を招く原因となります。適切な列に対して索引を作成し、定期的に統計情報を更新し、不要な索引は削除するなど、効果的な運用を心がけましょう。索引を正しく管理することで、データベースのパフォーマンス向上に大いに貢献することができます。
この情報を参考に、データベース設計の際に適切な索引運用を実践してください！
[参考]
データベース管理者ガイド – 21 索引の管理
SQL言語リファレンス – CREATE INDEX
SQL言語リファレンス – DROP INDEX
コメント