2章:where句
3.遅いインデックス パートⅡ
要点
- Oracle の CBO（コストベースオプティマイザ）は、複数のアクセスパス（単列インデックス群、複合インデックス、フルスキャン、ビットマップ 等）を比較して総コストが最小のプランを選ぶ
- INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID の組合せが多くの ROWID を返すと、行ごとのテーブルアクセスで総コストが跳ね上がる（＝遅くなる）
- オプティマイザは統計（列の分布、行数、NULL率等）に基づいて「INDEX の結果が何行になるか」を推定する（EXPLAIN の
Rows列に出る）。この推定が実データと乖離すると非最適な選択をする - ヒントは「どのアクセスパスを強制したらどうなるか」を比較する診断ツール。最終的な解決は統計更新や適切な（複合）インデックス設計によることが多い
概念図（アクセスパス比較のイメージ）
[SELECT ... WHERE subsidiary_id = 30 AND last_name = 'SATO']
|
--------------------------------------------------------------
| | | |
Index A Index B Index C Full table
(idx_subs) (idx_last) (idx_pk) (TABLE FULL SCAN)
| | | |
INDEX RANGE SCAN INDEX RANGE SCAN INDEX RANGE SCAN SEQUENTIAL READ
| | |
ROWID list -> TABLE ACCESS BY INDEX ROWID (fetch & filter) -> final rows
\____________________ Oracle CBO compares total cost ___________________/
-> choose min cost plan
典型的な診断／対応手順（Oracle）
1. EXPLAIN PLAN FOR ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ALL'));でBEFOREプランを取得
2. ヒントでインデックス使用を強制／禁止し、想定の経路ごとのコストを比較（例：/*+ INDEX(t idx_subs) */、/*+ NO_INDEX(t EMPLOYEES_PK) */）
3. DBMS_STATS.GATHER_TABLE_STATS(...) で統計を最新化（CBOが正しい見積を行えるように）
4. 必要なら複合インデックス（例：(subsidiary_id, last_name)）を作成して、INDEX RANGE SCANでより少ないROWIDを返すようにする。
5. AFTERプランを再取得してコストが下がったことを確認。
診断手順
事象：インデックスを変更したら、一部のクエリが遅くなった。
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
subsidiary_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
... -- その他の列
);
-- 変更前に存在していたインデックス例（単列）
CREATE INDEX idx_subs ON employees(subsidiary_id);
1) BEFORE：現状の実行計画
まず何もせずに実行計画を取ったときの典型的な出力
-- EXPLAIN PLAN... の想定出力（BEFORE）
Plan hash value: 111111111
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost | Time |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 477 |
| 1 | TABLE ACCESS FULL | EMP | 1000 | 477 |
----------------------------------------------------------
-
解釈：
CBOは TABLE FULL SCAN（コスト = 477） を選んでいる。 -
理由（想定）：
subsidiary_id単独のインデックスを使っても、インデックス→ROWID→テーブルアクセスの総コストがフルスキャンより高いと推定されたため。
2) ヒントで強制して比較（診断）
ヒントでインデックス利用を強制するとどんなプランになるかを確認する（診断目的）。
EXPLAIN PLAN FOR
SELECT /*+ INDEX(e idx_subs) */ first_name, last_name
FROM employees e
WHERE subsidiary_id = 30
AND last_name = 'SATO';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
想定出力（ヒント強制）
Plan hash value: 222222222
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost | Note |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 680 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1000 | 660 |
| 2 | INDEX RANGE SCAN | IDX_SUBS | 1000 | 20 |
-------------------------------------------------------------------------------
-
INDEX RANGE SCAN（IDX_SUBS）でsubsidiary_id=30に該当する最初のエントリを探し、葉ノードのチェーンを辿って多数のROWIDを返している - そのROWID群に対して
TABLE ACCESS BY INDEX ROWIDによりテーブル行を1行ずつ読み出す。last_name = 'SATO'のフィルタはテーブルから読み出した後に適用される（インデックスにlast_nameが含まれていないため） - この診断結果の合計コスト（ここでは想定で 680）は、BEFORE のフルスキャンコスト 477 より大きい → 実際の実行ではフルスキャンを選ぶ（＝結果的に遅くなる）
要点：
インデックスは「使えるが遅い」ケースがある — 特にインデックスが返すROWIDが多く、かつテーブルアクセスが必要な場合。
3) 原因の論理的整理
1. 索引が返す ROWID 数が多い
-
subsidiary_id = 30が多数の従業員を持つ場合（例：1000行）、INDEX RANGE SCAN は多数のROWIDを返す
2. フィルタの順序問題（インデックスにフィルタ列がない）
-
last_nameによる絞り込みがインデックス側で効かないため、テーブルアクセス後に絞り込まれる
3. オプティマイザの見積り（Rows列）が重要
- デフォルト統計やデフォルト仮定（中程度の選択性）により、CBOは「INDEX RANGE SCAN は約40行を返す」と見積もることがある。実際は1000行で乖離。推定と実績が乖離すると非最適な選択につながる
4. 完璧に遅くする組合せ
- 「広い範囲のインデックス探索（多くのROWID）＋多量の行を1行ずつテーブル読み出す（TABLE ACCESS BY INDEX ROWID）」が最悪のパターン
4) 対策の順序（診断→修正の推奨順）
1. 統計更新（まずはオプティマイザに正しい情報を与える）
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'YOUR_SCHEMA',
tabname => 'EMPLOYEES',
cascade => TRUE, -- インデックス統計も収集
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);
END;
/
→ 統計が古いとCBOの推定が大きく外れるため、まずは実データに基づく再評価を行う。
2. ヒントで強制してコスト構成を観察（既に診断で行った操作）
-
INDEX（インデックス強制）／NO_INDEX（インデックス禁止）でBEFORE/forced経路を比較し、ROWID数やTABLE ACCESS BY INDEX ROWIDのコストがどの程度かを把握する
3. 複合インデックス作成（解決策）
-
last_nameをインデックスに含めることで、INDEX段階でsubsidiary_id+last_nameの両方を絞り込み、返されるROWID数を劇的に減らせる。典型的には次を作る：
-- 基本：subsidiary_id と last_name を複合
CREATE INDEX emp_idx_sub_last ON employees(subsidiary_id, last_name);
-- もし SELECT で first_name, last_name だけを取得するようなケースで
-- テーブルアクセスを完全に避けたいなら、カバリング的に必要列も追加することを検討
-- （ただし Oracle のインデックスサイズ増は考慮する）
CREATE INDEX emp_idx_sub_last_first ON employees(subsidiary_id, last_name, first_name);
4. AFTER：複合インデックス作成後に再度 EXPLAIN
想定される改善後の実行計画（AFTER）：
Plan hash value: 333333333
-----------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 25 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 10 | 25 |
| 2 | INDEX RANGE SCAN | EMP_IDX_SUB_LAST| 10 | 5 |
-----------------------------------------------------------
-
EMP_IDX_SUB_LASTのINDEX RANGE SCANによりsubsidiary_id=30 AND last_name='SATO'が効率よく絞り込まれ、返されるROWIDは少数（例: 10行）になるため、テーブルアクセスは小規模で済みトータルコストは大幅に低下
※さらに、もし取得する列が全てインデックスに含まれていれば Oracle は Index-only（索引のみで完結） のパスを採ることができ、さらに高速化する。
なぜ「インデックスがあるのに遅くなる」のか
-
戻されるROWIDの量とその後のテーブルアクセスの量がキー。 インデックスが返すROWIDが多ければ多いほど、
TABLE ACCESS BY INDEX ROWIDが高コストになり得る - オプティマイザは「返されるROWID数の見積り」を統計から行い、その見積コストに従って最終判断をする（
RowsとCost列）。見積りが実際と大きく異なると、意図しないプラン（フルスキャン等）を選ぶ - ヒントは比較診断の道具だが、
根本は統計の正確さとインデックス設計（特に複合化やカバリング）