【Oracle】インデックス完全ガイド｜B-Tree・ビットマップ・複合・関数ベース・使われない条件・REBUILD

【Oracle】インデックス完全ガイド｜B-Tree・ビットマップ・複合・関数ベース・使われない条件・REBUILD Oracle

インデックスはクエリのパフォーマンスを劇的に改善する一方、設計を誤ると「作ったのに使われない」「更新が遅くなる」という問題が発生します。この記事では Oracle のインデックス種類・作成方法・設計のポイント・よくある落とし穴・メンテナンス手順を体系的に解説します。

この記事でわかること

  • B-Tree・ビットマップ・複合・関数ベース・UNIQUE インデックスの違いと作成方法
  • インデックスが使われない条件（関数適用・暗黙変換・LIKE 後方一致・列の NOT NULL）
  • INVISIBLE インデックスで本番への影響なしにテストする方法
  • REBUILD・COALESCE による断片化解消と統計情報の更新
  • 実行計画（EXPLAIN PLAN）でインデックスの使用状況を確認する方法
  • 12c 以降の部分インデックス（パーティション）と索引圧縮
スポンサーリンク

Oracle インデックスの種類

種類 用途 特徴
B-Tree インデックス 一般的な等値・範囲検索 デフォルト。高カーディナリティ列に有効
ビットマップインデックス 低カーディナリティ列の分析系クエリ 列の値の種類が少ない場合（性別・区分など）に有効。DWH向け
複合（コンポジット）インデックス 複数列を組み合わせた検索 先頭列が WHERE に含まれる場合に使用される
関数ベース（FBI）インデックス 式・関数の結果を検索 UPPER(col) = ‘値’ などの関数条件に使用される
UNIQUE インデックス 一意性の保証 PRIMARY KEY・UNIQUE 制約で自動作成
部分インデックス（Partial） 特定の値のみインデックス化 パーティション表で UNUSABLE パーティションを除外（12c〜）

CREATE INDEX：インデックスの作成

B-Tree インデックスの作成（基本）
-- 単一列インデックス
CREATE INDEX idx_emp_lastname
    ON employees (last_name);

-- 降順インデックス（大きい値から検索・ORDER BY DESC に有効）
CREATE INDEX idx_emp_hire_desc
    ON employees (hire_date DESC);

-- UNIQUE インデックス（重複禁止）
CREATE UNIQUE INDEX idx_emp_email_uniq
    ON employees (email);

-- オンラインインデックス作成（ONLINE: 表ロックなし・本番向け）
CREATE INDEX idx_orders_status
    ON orders (status)
    ONLINE;             -- 作成中もDML可能（若干オーバーヘッドあり）
複合インデックスの作成
-- 複合インデックス: WHERE department_id = ? AND job_id = ? のような検索に有効
-- 列の順序が重要: 先頭列 (department_id) は必ず WHERE に含まれていること
CREATE INDEX idx_emp_dept_job
    ON employees (department_id, job_id);

-- 先頭列が WHERE にない場合はインデックスが使われない（注意）
-- OK: WHERE department_id = 50                   → idx_emp_dept_job を使用
-- OK: WHERE department_id = 50 AND job_id = 'SA_REP' → idx_emp_dept_job を使用
-- NG: WHERE job_id = 'SA_REP'（先頭列 department_id なし）
--     → フルスキャンになる（SKIP SCAN が発生する場合を除く）
関数ベースインデックス（FBI）の作成
-- UPPER(last_name) で検索する場合は、列に直接インデックスを作っても意味がない
-- → UPPER(last_name) の結果にインデックスを作成する
CREATE INDEX idx_emp_upper_last
    ON employees (UPPER(last_name));

-- 使い方: UPPER() をそのまま WHERE 条件に使う
SELECT * FROM employees
WHERE UPPER(last_name) = 'KING';   -- → idx_emp_upper_last が使われる

-- NVL や式にも使える
CREATE INDEX idx_orders_nvl_close
    ON orders (NVL(close_date, DATE '9999-12-31'));

-- JSON_VALUE にも有効（Oracle 12c 以降）
CREATE INDEX idx_prod_brand
    ON products (JSON_VALUE(attributes, '$.brand'));
ビットマップインデックスの作成（DWH・分析系向け）
-- ビットマップインデックスは列の値の種類が少ない（低カーディナリティ）場合に有効
-- 例: status = 'ACTIVE'|'INACTIVE'|'PENDING' の3値
CREATE BITMAP INDEX idx_orders_status_bmp
    ON orders (status);

-- 注意: ビットマップインデックスはOLTP（更新頻度の高い表）には不向き
-- → 行ロックではなくビット列ロックになり、同時更新で競合しやすい
-- → DWH（分析・集計専用）の表にのみ使用すること

インデックスが使われない落とし穴

インデックスを作成しても、クエリの書き方によっては使われないことがあります。以下のパターンを把握しておくことで、チューニング時の原因特定が速くなります。

NG: 関数・演算でインデックス列を加工している
-- NG: TRUNC(hire_date) にはインデックスがないため hire_date インデックスは使われない
SELECT * FROM employees WHERE TRUNC(hire_date) = DATE '2010-01-15';

-- OK: 範囲条件で関数を使わずに書き直す
SELECT * FROM employees
WHERE hire_date >= DATE '2010-01-15'
  AND hire_date <  DATE '2010-01-16';

-- NG: TO_CHAR(hire_date, 'YYYY') = '2010' → hire_date インデックスを使えない
-- OK: hire_date >= DATE '2010-01-01' AND hire_date < DATE '2011-01-01'

-- NG: salary * 1.1 > 60000 → salary インデックスを使えない
-- OK: salary > 60000 / 1.1  （右辺に定数計算を移動）
NG: 暗黙型変換が発生している
-- employee_id は NUMBER 型なのに文字列で比較している
-- Oracle は文字列→NUMBER に暗黙変換するが、索引列側に変換が走りインデックスが無効になることがある
-- NG: WHERE employee_id = '100'  （文字列で比較）
-- OK: WHERE employee_id = 100     （NUMBER で比較）

-- phone_number は VARCHAR2 型なのに NUMBER で比較している
-- → 文字列列に TO_NUMBER() 相当の変換が走りインデックスが使われない
-- NG: WHERE phone_number = 5150127368
-- OK: WHERE phone_number = '515.127.3368'

-- 日付型の列を文字列で比較
-- NG: WHERE hire_date = '2010-01-15'   → 暗黙変換（NLS_DATE_FORMAT に依存）
-- OK: WHERE hire_date = DATE '2010-01-15'   または TO_DATE('2010-01-15','YYYY-MM-DD')
NG: LIKE の前方ワイルドカード・否定条件
-- NG: LIKE '%King' → 先頭がワイルドカードはインデックスを使えない
SELECT * FROM employees WHERE last_name LIKE '%King';

-- OK: 前方一致はインデックスが使われる
SELECT * FROM employees WHERE last_name LIKE 'Ki%';

-- NG: IS NULL → B-Tree インデックスは NULL を格納しないため IS NULL では使われない
SELECT * FROM employees WHERE commission_pct IS NULL;
-- ↑ 大量の NULL が含まれる場合はビットマップ or 別の設計を検討

-- NG: NOT IN, !=, <> → 否定条件は原則インデックスを使いにくい
SELECT * FROM employees WHERE department_id != 50;
-- ↑ 多くの行が返る場合は FTS の方が速いためオプティマイザが選ばない
NULL とインデックス
Oracle の B-Tree インデックスは NULL をエントリとして格納しません。そのため、IS NULL 条件ではインデックスが使われません。NULL を含む列を頻繁に検索する場合は、NVL(col, 0) のような関数ベースインデックスを作成するか、NULL を別の値（例：DEFAULT 0）で管理するよう設計を見直してください。

INVISIBLE インデックス：本番影響なしでテストする

INVISIBLE インデックスはオプティマイザから見えない状態で作成できます。本番環境でインデックスを作成しても既存のクエリに影響を与えず、セッション単位でのみ有効にして効果を確認できます。

INVISIBLE インデックスの作成と有効化
-- INVISIBLE で作成: オプティマイザは通常この索引を無視する
CREATE INDEX idx_emp_dept_invisible
    ON employees (department_id)
    INVISIBLE;

-- セッション単位で INVISIBLE インデックスを有効にする（テスト用）
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- 効果を確認したら本番でも有効化
ALTER INDEX idx_emp_dept_invisible VISIBLE;

-- 逆に既存インデックスを一時的に無効化（本番影響なし）
ALTER INDEX idx_emp_lastname INVISIBLE;

-- インデックスの可視状態を確認
SELECT index_name, visibility, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name;

実行計画でインデックスの使用状況を確認する

EXPLAIN PLAN で実行計画を確認する
-- EXPLAIN PLAN でクエリの実行計画を確認
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'King';

-- 実行計画の表示
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 結果例（インデックスが使われている場合）:
-- | Id | Operation                    | Name               |
-- |  0 | SELECT STATEMENT             |                    |
-- |  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES          |
-- |  2 |   INDEX RANGE SCAN           | IDX_EMP_LASTNAME   |

-- フルスキャンになっている場合:
-- | Id | Operation         | Name      |
-- |  0 | SELECT STATEMENT  |           |
-- |  1 |  TABLE ACCESS FULL| EMPLOYEES |

-- ヒント句で強制的にインデックスを使わせる（テスト用）
SELECT /*+ INDEX(e idx_emp_lastname) */ *
FROM employees e WHERE last_name LIKE 'Ki%';
INDEX RANGE SCAN と INDEX FULL SCAN の違い

  • INDEX RANGE SCAN: = や BETWEEN・LIKE ‘前方%’ など範囲が限定される場合
  • INDEX UNIQUE SCAN: UNIQUE インデックスに対して = 条件（1行確定）
  • INDEX FULL SCAN: インデックスの全エントリをスキャン（TABLE ACCESS FULL より速い場合がある）
  • INDEX FAST FULL SCAN: インデックスをマルチブロック読みでフルスキャン（ORDER BY なし）

インデックスのメンテナンス：REBUILD と COALESCE

頻繁な INSERT・UPDATE・DELETE を繰り返すと、インデックスのリーフブロックに空きが増えて断片化します。断片化を解消するには REBUILD または COALESCE を使います。

REBUILD・COALESCE・UNUSABLE の操作
-- インデックスを再構築（ブランチ・リーフ全体を再作成）
-- → 統計情報も更新される。ONLINE を付けると DML を止めない
ALTER INDEX idx_emp_lastname REBUILD;
ALTER INDEX idx_emp_lastname REBUILD ONLINE;   -- 本番推奨

-- COALESCE: リーフブロックの空きをまとめる（REBUILD より軽量・高速）
-- → ブランチ構造は変わらない。断片化が軽微な場合に有効
ALTER INDEX idx_emp_lastname COALESCE;

-- インデックスを一時的に無効化（大量 INSERT 前のパフォーマンス改善）
ALTER INDEX idx_emp_lastname UNUSABLE;
-- 大量 INSERT 後に再構築
ALTER INDEX idx_emp_lastname REBUILD;

-- 断片化の確認（ANALYZE INDEX）
ANALYZE INDEX idx_emp_lastname VALIDATE STRUCTURE;
SELECT name, height, lf_blks, del_lf_rows, del_lf_rows_len
FROM index_stats;
-- del_lf_rows / lf_rows が 20%〜30% を超えたら REBUILD を検討
インデックスの削除・名前変更・統計情報の更新
-- インデックスの削除
DROP INDEX idx_emp_lastname;

-- インデックスの名前変更（Oracle 9i 以降）
ALTER INDEX idx_emp_dept_job RENAME TO idx_emp_dept_job_v2;

-- インデックスの統計情報を手動で更新（DBMS_STATS を使用）
EXEC DBMS_STATS.GATHER_INDEX_STATS(
    ownname => USER,
    indname => 'IDX_EMP_LASTNAME'
);

-- テーブルの全インデックスをまとめて統計更新
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'EMPLOYEES',
    cascade => TRUE    -- インデックス統計も同時に更新
);

インデックス設計のポイント

  • カーディナリティが高い列（種類が多い列）ほど B-Tree インデックスの効果が高い。社員番号・メールアドレスなど一意に近い列は特に有効
  • WHERE に頻繁に登場する列を優先。全件取得のような使われ方には効果がない
  • 複合インデックスの列順は「絞り込み効果の高い列を先頭に」かつ「WHERE に毎回登場する列を先頭に」
  • カバリングインデックス: SELECT 列もインデックスに含めると表へのアクセスなしで結果を返せる（INDEX-ONLY SCAN 相当）
  • インデックスは更新コストがある。INSERT/UPDATE/DELETE のたびにインデックスも更新される。不要なインデックスは削除する
  • 結合列（JOIN の ON 句）は必ずインデックスを検討する
カバリングインデックス（インデックス内に SELECT 列を含める）
-- employees から department_id=50 の社員 ID・名前だけ取得する場合
-- 通常: idx_emp_dept（department_id のみ）→ インデックスから ROWID を取得後に表へアクセス
-- カバリング: インデックスに employee_id と last_name も含めると表アクセスが不要になる
CREATE INDEX idx_emp_dept_covering
    ON employees (department_id, employee_id, last_name);

SELECT employee_id, last_name
FROM employees
WHERE department_id = 50;
-- → TABLE ACCESS BY INDEX ROWID なし: INDEX RANGE SCAN のみで完結

まとめ

  • B-Tree インデックス：一般用途。高カーディナリティ列への等値・範囲検索に有効
  • ビットマップインデックス：低カーディナリティ列の DWH 環境向け。OLTP には不向き
  • 関数ベースインデックス（FBI）：UPPER/TRUNC などの式条件を高速化
  • 使われない条件：関数でのラップ・暗黙変換・LIKE 後方一致・IS NULL に注意
  • INVISIBLE インデックス：本番影響なしで効果をテストできる
  • REBUILD / COALESCE：断片化解消。大量 DML 後は del_lf_rows の割合を確認する

実行計画の見方・SQL ヒント句で特定インデックスを強制する方法はSQLヒント句の使い方完全ガイドを、インデックスの使用状況を監視する方法（USER_INDEXES・V$OBJECT_USAGE）はインデックス情報を取得する方法完全ガイドも参照してください。