Oracle SQL 索引の基礎と作成手順｜高速化とチューニング

検索が遅い、システム全体のレスポンスが悪い。そんな時、最初に疑うべきは「索引（インデックス）」の設定です。本記事では Oracle 索引 の仕組みから、適切な作成基準、パフォーマンスチューニング に直結する実践的な検証手順までを解説します。初心者でも迷わず実装できるステップで、SQL高速化の第一歩を踏み出しましょう。

索引（インデックス）のメンテナンス方法まとめ

Oracleデータベースで性能を維持するうえで、索引の定期的なメンテナンスは非常に重要です。索引は検索性能を高める一方、以下のような課題を持っています。✅ 更新による断片化✅ 統計情報の陳腐化✅ 不要な索引の放置この記事では、Oracleに...

oracle-master00000.com

2025.08.24

---

1. 結論・最短手順（やることリスト）

まず、索引を作成して検索を高速化するための最短ルートを提示します。

1. 対象列の選定: WHERE句やJOIN条件で頻繁に使われる列、かつ「値のばらつき（カーディナリティ）」が多い列を選ぶ。
2. 現状確認: すでに索引が存在しないか確認する。
3. 索引作成: CREATE INDEX 文を実行する。
4. 効果検証: 実行計画または処理時間で改善を確認する。

---

2. 索引（Index）とは？ 仕組みとメリット

索引のイメージ

索引とは、本の「巻末索引」のようなものです。

索引がない場合、データベースは本の最初から最後まで全てのページ（行）をめくって探す「全表走査（Full Table Scan）」を行います。これに対し、索引があれば「キーワード → ページ番号（ROWID）」を即座に特定し、目的のデータへ直接アクセスできます。

索引のメリットとデメリット

特徴	メリット	デメリット
検索 (SELECT)	目的の行へピンポイントでアクセスできるため、劇的に高速化する。	(特になし)
更新 (DML)	(特になし)	データ追加・更新・削除のたびに索引も書き換えるため、処理負荷（オーバーヘッド）が増加する。
容量	(特になし)	索引データ自体がディスク領域を消費する。

ポイント: 「とりあえず全部の列に索引を作る」のはNGです。更新速度の低下を招くため、必要な列に絞る設計が重要です。

---

3. 索引の種類と使い分け（Oracle Database）

Oracleには複数の索引タイプがありますが、基本は B-Tree索引 です。

1. B-Tree索引（デフォルト）

• 用途: 最も一般的。ID、名前、日付など、値のバリエーションが多い列向け。
• 特徴: 木構造（ツリー）になっており、データ量が増えても検索計算量が安定している。
• SQL例: CREATE INDEX idx_emp_id ON employees(emp_id);

2. ビットマップ索引 (Bitmap Index)

• 用途: 「性別（男/女）」「都道府県」など、値の種類（カーディナリティ）が少ない列向け。※主にEnterprise EditionのDWH環境で使用。
• 特徴: データの更新（ロック）競合が発生しやすいため、頻繁に更新されるOLTPシステムには不向き。

3. 関数索引 (Function-Based Index)

• 用途: UPPER(name) や計算式など、関数を通した結果で検索する場合。
• 特徴: 検索条件と索引の定義が一致している必要がある。

4. 複合索引 (Composite Index)

• 用途: WHERE col1 = 'A' AND col2 = 'B' のように、常に複数列を組み合わせて検索する場合。
• 注意: 列の並び順が重要です（頻繁に絞り込みに使われる列を先頭にする）。

---

4. どの列に作成すべきか？（選定基準）

パフォーマンスチューニング の鍵は、「効果的な列」を選ぶことです。

1. WHERE句で「等価条件 (=)」や「範囲条件 (BETWEEN, > <)」に使われる列
   • 検索対象を大きく絞り込める列が最適です。
2. JOIN（結合）のキーとなる列
   • テーブル結合時のアクセスを高速化します。
3. カーディナリティ（値の種類）が高い列
   • 例：社員番号、メールアドレス（一意に近い値）。
   • 逆の例：性別、フラグ（「0か1」しかない列にB-Tree索引を作っても効果は薄い）。
4. NULLが多い列への注意
   • Oracleの標準的なB-Tree索引は、全てのキー列がNULLの行を索引に含めません。IS NULL 検索には工夫が必要です。

【Oracle設計実務】索引（インデックス）を作るべき理由と、作りすぎを避けるための設計ポイント

Oracleにおけるパフォーマンス改善といえば「索引（インデックス）」の活用が定番です。しかし現場では以下のような誤解も多く存在します。「WHERE句に出てくる列にはとりあえず全部インデックスを貼れば良い」「遅いならインデックスを増やせば解...

oracle-master00000.com

2025.08.10

---

5. 実践：索引の作成と削除手順

ここからは実機（SQL*Plus や SQL Developer等）で実行可能な手順です。

前提確認

• 対象スキーマに対する CREATE ANY INDEX 権限、または表の所有者であること。
• 表がロックされていないこと。

手順1: 既存の索引を確認する

重複作成を防ぐため、まずは現状を確認します。

-- Check existing indexes for a specific table
SELECT index_name, index_type, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- Check indexed columns
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

手順2: 索引を作成する

SQL内で日本語（マルチバイト文字）を使用しないように注意してください。

-- Basic B-Tree Index
CREATE INDEX idx_emp_name ON employees(name);

-- Composite Index (Order matters)
CREATE INDEX idx_dept_sal ON employees(department_id, salary);

-- Function-Based Index (e.g., for case-insensitive search)
CREATE INDEX idx_upper_name ON employees(UPPER(name));

手順3: 索引を削除する

不要になった索引は削除して、更新負荷を下げます。

-- Drop Index
DROP INDEX idx_emp_name;

---

6. 実行例：効果検証デモ

実際に10万件のデータを投入し、索引の有無で速度差を体感してみましょう。

※検証環境での実行を推奨します。

ステップ1: テスト用テーブルとデータの準備

-- 1. Create a table for testing
CREATE TABLE demo_employees (
    emp_id NUMBER,
    name VARCHAR2(100),
    dept_id NUMBER,
    salary NUMBER
);

-- 2. Insert 100,000 rows (PL/SQL loop)
BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO demo_employees 
        VALUES (i, 'User' || TO_CHAR(i), MOD(i, 10), i * 10);
    END LOOP;
    COMMIT;
END;
/

ステップ2: 索引なしでの検索

実行時間を計測します。

-- Enable timing
SET TIMING ON;

-- Search without index (Full Table Scan)
SELECT * FROM demo_employees WHERE name = 'User50000';

結果目安: データ量によりますが、0.1秒〜数秒かかり、テーブル全体を読み込みます。

ステップ3: 索引作成と再検索

-- Create Index on 'name' column
CREATE INDEX idx_demo_name ON demo_employees(name);

-- Search with index (Index Range Scan or Unique Scan)
SELECT * FROM demo_employees WHERE name = 'User50000';

結果目安: 0.01秒以下（数ミリ秒）。劇的に速くなります。

---

7. トラブルシューティング（よくあるエラー）

索引操作時によく遭遇するエラーとその対処法です。

エラーコード	エラー内容	原因と対処
ORA-00955	名前がすでに使用されています	同名の索引、あるいは同名のオブジェクトが既に存在します。名前を変更するか、既存を確認してください。
ORA-01418	指定した索引は存在しません	DROP 時に名前が間違っているか、別スキーマの索引を消そうとしています。
ORA-00001	一意制約(…)に反しています	CREATE UNIQUE INDEX を実行した際、テーブル内に既に重複データが含まれています。重複を解消するか、非ユニーク索引にしてください。
ORA-01654	表領域…を拡張できません	ディスク容量不足です。DBAに表領域の拡張を依頼してください。

---

8. 運用・監視上の注意点

「不可視索引 (Invisible Index)」の活用

Oracle 11g以降（19c含む）では、索引を削除せずに「一時的に無効化（オプティマイザから見えなくする）」機能があります。

「この索引を消しても性能に影響が出ないか？」を確認する際に便利です。

-- Make index invisible (Optimizer ignores it)
ALTER INDEX idx_demo_name INVISIBLE;

-- Make index visible again
ALTER INDEX idx_demo_name VISIBLE;

統計情報の更新

索引を作成しても、統計情報が古いままだとOracleが索引を使ってくれない場合があります。作成直後であれば自動収集されますが、大量データの入れ替え後は手動収集も検討してください。

---

9. FAQ：よくある質問

Q1. 索引は多ければ多いほど良いですか？

A. いいえ、逆効果になることがあります。

参照（SELECT）は速くなりますが、登録・更新・削除（INSERT/UPDATE/DELETE）のたびに索引メンテナンスが発生し、書き込み性能が劣化します。不要な索引は定期的に削除すべきです。

Q2. 索引を作成したのに使われません。なぜですか？

A. 主な原因は以下の通りです。

• カーディナリティが低い: 全体の5〜15%以上の行がヒットする場合、Oracleは索引より全表走査の方が速いと判断することがあります。
• 列の加工: WHERE UPPER(name) = 'A' のように列に関数を使うと、通常の索引は使えません（関数索引が必要です）。
• 統計情報が古い: オプティマイザが古い情報を元に判断している可能性があります。

Q3. 主キー（Primary Key）に索引は必要ですか？

A. 自動的に作成されます。

主キー制約または一意制約（Unique）を定義すると、Oracleは自動的にユニーク索引を作成します。別途作成する必要はありません。

---

10. まとめ

Oracleデータベースのパフォーマンスを維持するためには、適切な索引設計が不可欠です。

1. 検索条件や結合条件 になる列を特定する。
2. カーディナリティ（値のばらつき） が高い列を選ぶ。
3. 作成・更新コスト とのバランス（メリット・デメリット）を考慮する。
4. 実行計画 で実際に使われているか確認する。

まずは検証環境で索引を作成し、劇的な速度改善を体感してみてください。

[参考]
Oracle Database SQL言語リファレンス 19c

Oracleテーブル作成とデータ型をマスターする

Oracleデータベースにおけるテーブル作成は、効率的なデータ管理の基本です。本記事では、初心者でも理解しやすいように、テーブル作成の構文や主要なデータ型を具体例とともに詳しく解説します。また、現場で役立つヒントや注意点も盛り込みました。 ...

oracle-master00000.com

2024.12.17