Oracle SQL の検索速度が遅いと感じたとき、最初に検討すべき対策が「Oracle インデックス（索引）」の活用です。適切にインデックスを作成することで、数万〜数億件のデータアクセスを一瞬で完了させることが可能になります。
この記事では、インデックスの仕組みやB-tree構造の基礎から、実機（Oracle Database 19c）を用いた作成手順、そして「EXPLAIN PLAN FOR」を使った実行計画の正しい読み方までを徹底解説します。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
結論・最短手順（やることリスト）
SQLのパフォーマンス改善を急いでいる場合、以下のステップで進めます。
- 現状分析: 遅いSQLを特定し、WHERE句やJOIN条件の列を確認する。
- インデックス作成:
CREATE INDEX文で対象列にインデックスを付与する。 - 効果検証:
EXPLAIN PLANで実行計画を取得し、アクセスパスが「INDEX RANGE SCAN」等に変化したか確認する。
インデックス（索引）とは？ 仕組みと構造
検索用の「目次」
インデックスとは、書籍における「目次」と同じ役割を果たします。 目次がない本から特定の単語を探す場合、1ページ目から順に探す（全表走査：Full Table Scan）必要がありますが、目次があれば該当ページへ直接アクセスできます。
データベースも同様で、インデックスがないと全行を読み込みますが、インデックスがあれば「ROWID（行の住所）」を使ってピンポイントでデータを取り出せます。
B-tree インデックスの構造
Oracleで最も一般的に使われるのが「B-tree（バランス木）インデックス」です。データ量が増えても検索回数が計算できる（対数オーダー）ため、安定した性能を発揮します。
【B-tree 構造のイメージ】
[ルートブロック]
↓
+--------------+
| 中間ブロック |
+------+-------+
↓
+-------------------------------------------+
| リーフブロック (キー値 + ROWID) |
| ----------------------------------------- |
| 100 -> AAAB... (行の物理アドレス) |
| 101 -> AAAC... |
+-------------------------------------------+
メリットとデメリット（運用上の注意）
インデックスは万能ではありません。作成前に以下のトレードオフを理解しておく必要があります。
| 項目 | 特徴 |
|---|---|
| メリット | ・SELECT（検索）、結合、ORDER BY（ソート）が劇的に高速化する。・一意性制約（Primary Key/Unique）の強制に必須。 |
| デメリット | ・DML（INSERT/UPDATE/DELETE）が遅くなる（データと同時に索引も書き換えるため）。・ディスク領域（表領域）を消費する。・不要な索引はオプティマイザの判断を迷わせる原因になる。 |
インデックスの種類
Oracleには用途に応じた複数のインデックスがありますが、まずは基本のB-treeを押さえましょう。
- B-treeインデックス
- デフォルトの形式。カーディナリティ（値の種類）が高い列（ID、名前、日付など）に最適。
- ビットマップインデックス
- 性別や都道府県など、値の種類が少ない列向け。更新頻度が高いテーブルには不向き（ロック競合の原因になる）。
- 関数ベースインデックス
UPPER(emp_name)のように、列を加工した状態で検索する場合に使用。
- 複合インデックス
WHERE col1 = 'A' AND col2 = 'B'のように、複数列を組み合わせた検索を高速化。
実装：インデックスの作成・確認・削除
ここでは最も基本となる B-tree インデックスの操作を行います。 ※SQL内のコメントは英語記述としています（マルチバイト文字排除のため）。
1. インデックスの作成 (CREATE INDEX)
-- Syntax: CREATE INDEX index_name ON table_name(column_name);
-- 1. Simple B-tree Index
CREATE INDEX emp_idx ON employees(emp_id);
-- 2. Composite Index (Multi-column)
CREATE INDEX emp_name_dept_idx ON employees(emp_name, dept_id);
-- 3. Function-based Index
CREATE INDEX emp_upper_idx ON employees(UPPER(emp_name));
2. インデックスの確認
作成されたインデックスはデータディクショナリ USER_INDEXES や USER_IND_COLUMNS で確認します。
-- Check index status and validity
SELECT index_name, table_name, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
-- Check columns in the index
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;
3. インデックスの削除 (DROP INDEX)
不要になった、または作り直す場合は削除します。
-- Drop Index
DROP INDEX emp_idx;
実行例：インデックスの効果を実行計画で確認する
インデックスは「作っただけ」では意味がありません。Oracleが実際に使ってくれたかを確認する必要があります。ここではダミーデータを作成し、実行計画の変化を確認します。 ※検証環境: Oracle Database 19c Enterprise Edition (PDB環境)
Step 1: 検証用テーブルとデータの準備
まず、プライマリキーなどを設定しない単純なヒープ表を作成し、10万件のデータを投入します。
-- 1. Create a test table
CREATE TABLE test_index_demo (
id NUMBER,
name VARCHAR2(100),
code VARCHAR2(10)
);
-- 2. Insert 100,000 rows
-- Using PL/SQL loop for clear demonstration
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test_index_demo
VALUES (i, 'User_' || i, 'CD' || MOD(i, 100));
END LOOP;
COMMIT;
END;
/
Step 2: 実行計画の取得方法 (EXPLAIN PLAN FOR)
SQLがどのように実行されるか（パス）を確認するには EXPLAIN PLAN FOR コマンドを使用します。
実行手順:
- 解析したいSQLの直前に
EXPLAIN PLAN FORを付けて実行する（これで計画が保存されます）。 DBMS_XPLAN.DISPLAY関数で保存された計画を表示する。
-- 1. Generate execution plan (Does not execute the query, only plans it)
EXPLAIN PLAN FOR
SELECT * FROM test_index_demo WHERE name = 'User_99999';
-- 2. Display the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
Step 3: 実行計画の見方（初心者向け）
上記を実行すると、以下のような表形式の結果が出力されます。最初は複雑に見えますが、見るべきポイントは3つだけです。
【インデックス作成前の実行計画例】
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM test_index_demo WHERE name = 'User_99999';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 4127716390
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 432 | 103 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_INDEX_DEMO | 6 | 432 | 103 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='User_99999')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
17 rows selected.
チェックポイント:
- Operation (操作): ここが最重要です。
- TABLE ACCESS FULL: 全表走査（インデックスを使わず、全データを読んでいる）。データ量が多いと遅くなります。
- INDEX RANGE SCAN / UNIQUE SCAN: インデックスを使っています。
- Rows (行数): Oracleが「この処理で何行ヒットしそうか」と予測した数です。
- Cost (コスト): 処理の重さを数値化したもの。低いほど高速（基本的には）です。ここでは
103です。
Step 4: インデックス作成後の変化
では、name 列にインデックスを作成し、再度計画を確認します。
-- Create Index on 'name' column
CREATE INDEX idx_test_name ON test_index_demo(name);
-- Check Plan again
EXPLAIN PLAN FOR
SELECT * FROM test_index_demo WHERE name = 'User_99999';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
【インデックス作成後の実行計画例】
SQL> CREATE INDEX idx_test_name ON test_index_demo(name);
Index created.
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM test_index_demo WHERE name = 'User_99999';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2000019570
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_INDEX_DEMO | 1 | 72 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='User_99999')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
変化のポイント:
- Operation:
TABLE ACCESS FULLが消え、INDEX RANGE SCANになりました。これは「インデックスの範囲スキャンを行って該当キーを探し、そのROWIDを使ってテーブルにアクセスした」ことを意味します。 - Name: 作成したインデックス名
IDX_TEST_NAMEが表示されています。 - Cost:
103から2へ激減しました。これがパフォーマンス向上の証拠です。
トラブルシューティング（よくあるエラーと対策）
インデックス操作時によく遭遇するエラーとその対処法です。
| エラーコード | エラーメッセージ | 原因と対処 |
|---|---|---|
| ORA-01418 | 指定した索引は存在しません | DROP INDEX などを実行しようとしたが、インデックス名が間違っているか、既に削除されています。USER_INDEXES を確認してください。 |
| ORA-00001 | 一意制約(…)に反しています | ユニークインデックスを作成しようとした列に、重複データが存在します。重複を削除するか、通常の（非ユニーク）インデックスに変更してください。 |
| ORA-01654 | 表領域…を拡張できません | インデックスを格納する表領域が満杯です。データファイルを拡張するか、不要なオブジェクトを削除してください。 |
「インデックスを作ったのに使われない」場合
以下のようなケースでは、Oracleのオプティマイザが「インデックスを使わない方が速い（あるいは使えない）」と判断することがあります。
- 検索対象が全件の大部分（20%〜以上など）を占める: 全表走査の方がI/Oが少なくて済むため。
- LIKE演算子で前方一致していない:
WHERE name LIKE '%User'（中間・後方一致）はインデックスが効きません。 - 列に対して演算を行っている:
WHERE UPPER(name) = 'USER_1'など。この場合は関数ベースインデックスが必要です。 - 統計情報が古い:
DBMS_STATS.GATHER_TABLE_STATSで統計情報を更新してください。
運用・セキュリティ上の注意
インデックスの断片化と再構築
頻繁に INSERT / DELETE が繰り返されると、B-treeインデックス内部がスカスカの状態（断片化）になり、性能が劣化します。定期的に「再構築（Rebuild）」を検討しますが、ロックがかかる点に注意が必要です。
-- Rebuild index (Caution: Locks the table unless ONLINE is specified)
ALTER INDEX idx_test_name REBUILD ONLINE;
※ ONLINE キーワードを付けることで、参照・更新を止めずに再構築可能です（Enterprise Edition機能）。
不可視インデックス (Invisible Index)
インデックスを削除する際、いきなり DROP するのが怖い場合は、「不可視（Invisible）」状態にして影響を確認することが推奨されます。
-- Make index invisible to optimizer
ALTER INDEX idx_test_name INVISIBLE;
FAQ：よくある質問
Q1. 1つのテーブルにインデックスはいくつまで作って良いですか？
A. 明確な上限はありませんが、3〜5個程度を目安にするのが一般的です。多すぎると INSERT/UPDATE 性能が著しく劣化します。必要なものだけを厳選してください。
Q2. 主キー（Primary Key）にもインデックス作成は必要ですか？
A. 不要です。主キー制約を定義すると、Oracleは自動的にユニークインデックスを作成します（既存のインデックスが流用される場合もあります）。
Q3. インデックス作成はどのくらい時間がかかりますか？
A. データ量とサーバー性能に依存します。数百万件以上ある場合、数分〜数十分かかることもあります。本番環境で実施する場合は、利用者が少ない夜間帯に行うか、ONLINE オプションの使用を推奨します。
まとめ
Oracleのパフォーマンスチューニングにおいて、インデックスは最も効果的かつ基本的な手段です。
- 仕組み: 本の目次と同様、ROWIDを使ってデータへ高速アクセスする。
- 基本: 通常は B-treeインデックス を使用する。
- 作成:
CREATE INDEXで作成し、DROP INDEXで削除する。 - 確認: 必ず EXPLAIN PLAN を取得し、Operationが
TABLE ACCESS FULL（全表走査）からINDEX RANGE SCAN等に変わったか確認する。 - 注意: 作りすぎは更新性能を下げるため、必要な列に絞る。
まずは開発環境で EXPLAIN PLAN FOR を使い、自分のSQLが意図通りインデックスを使っているか確認することから始めてみてください。
[参考]
データベース管理者ガイド – 21 索引の管理
SQL言語リファレンス – CREATE INDEX
SQL言語リファレンス – DROP INDEX
本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント