Oracle B-treeインデックスアクセス

はじめに

B-treeインデックスを使用したテーブルアクセスの方法についてまとめます。

B-treeインデックスアクセス方式

操作	ヒント	代替ヒント
INDEX FULL SCAN	INDEX
INDEX FULL SCAN (MIN/MAX)	INDEX
INDEX FULL SCAN DESCENDING	INDEX_DESC
INDEX RANGE SCAN	INDEX	INDEX_RS_ASC
INDEX RANGE SCAN DESCENDING	INDEX_DESC	INDEX_RS_DESC
INDEX RANGE SCAN (MIN/MAX)	INDEX
INDEX SKIP SCAN	INDEX_SS	INDEX_SS_ASC
INDEX SKIP SCAN DESCENDING	INDEX_SS_DESC
INDEX UNIQUE SCAN	INDEX	INDEX_RS_ASC
INDEX FAST FULL SCAN	INDEX_FFS
INDEX SAMPLE FAST FULL SCAN	INDEX_FFS
HASH JOIN	INDEX_JOIN
AND-EQUAL	AND_EQUAL

各アクセス方法の概要

INDEX FULL SCAN・INDEX FULL SCAN (MIN/MAX)・INDEX FULL SCAN DESCENDING

▽特徴

• リーフ・ブロックを全て走査し、条件に合致するエントリを返却
• リンク順にスキャンするため、キー値でソートされた値にエントリを返却
• 返却値がソートされているため、その後のソートを回避できる可能性がある

▽SQLの条件

• INDEX FULL SCANは、SQLの条件で索引列の値が選択されている必要はありません。
• 索引のFULLスキャンが選択される条件は、SQLで参照される列が全て索引に含まれている場合、かつ少なくても索引列の一つにNOT NULL制約が付いている場合です。
• INDEX FULL SCANが選択されるのは、索引列のみの参照でソート処理が必要な場合に、表データをFULLスキャンしてソートするのではなく、ソート済みの索引のFULLスキャンしてソート処理が回避できます。ルート、ブランチブロックからリーフブロック全体を検索します。

▽留意点

• INDEXの全ブロックがシーケンシャルアクセス(1ブロックずつ読み込まれる)される

INDEX RANGE SCAN・INDEX RANGE SCAN DESCENDING・INDEX RANGE SCAN (MIN/MAX)

▽特徴

• キー値の範囲でリーフ･ブロックを走査し、条件に該当する複数エントリを返却
• ルート、ブランチブロックから選択範囲内のリーフブロックを検索する
• 該当するリーフブロック内のROWIDをもとに実際の表データを参照
• SQLが索引列のみを必要としている場合は、表データへの参照は行われず、リーフブロック内に格納されているデータから値を返す

▽SQLの条件

• 索引が定義された列に対して範囲条件(例：BETWEEN,<,>)を指定した場合
• 非一意索引の場合は、等価条件でもINDEX RANGE SCANが選択される

▽留意点

• 索引は索引列がソートして格納されているため、索引を使用してORDER BY句を満たせる場合は、ソート処理を回避することも可能

INDEX SKIP SCAN・INDEX SKIP SCAN DESCENDING

▽特徴

• 複数列で作成されたINDEXが、SQLの条件内でINDEX列の第一列が指定されていない場合でもINDEXスキャンを実施することが可能
• 内部的に、第一列の値をもとに論理的な副索引を作成して索引スキャンを可能にしている
• 第一列の値をもとに副索引が作成されるため、第一列の値の個別値が非常に少ない場合に有効に動作する
• 索引数を減らすことで、表データの更新処理を効率化することも可能

▽SQLの条件

• 複合索引の第一列目に対する条件指定が無く、2列目以降の列に対して条件指定があった場合に採用される可能性がある

▽留意点

• INDEX RANGE SCANと比較して効率が悪いアクセス方法

INDEX UNIQUE SCAN

▽特徴

• 条件に合致する1エントリを返却
• ルート、ブランチブロックから単一値の範囲内のリーフブロックを検索
• 該当するリーフブロック内の１つのROWIDをもとに実際の表データを参照する
• SQLがINDEX列のみを必要としている場合には、表データへの参照は実行されず、リーフブロック内に格納されているデータから値を返す

▽SQLの条件

• 単一の値が選択されるような場合(列が等価条件で指定されている)に使用される

▽留意点

• 単一の値が選択される条件としては、該当する表の列に対し、一意制約または主キー制約が存在する必要がある

INDEX FAST FULL SCAN・INDEX SAMPLE FAST FULL SCAN

▽特徴

• ツリー構造を意識せずに、セグメント・ヘッダから順にブロックをフルスキャンする
• 返却値はソートされていない
• マルチ・ブロック・リードやパラレル実行が可能

▽SQLの条件

• SQLの条件で、索引列の値が選択されている必要はない
• INDEX FAST FULL SCAN選択の条件は、SQLで参照される列が全て索引に含まれている場合、かつ少なくとも索引列の一つがNOT NULL制約が付いている場合
• 索引のFULLスキャンと同じ条件

▽留意点

• INDEX FULL SCANとの差異としてはソート処理の有無。INDEX FULL SCANは、索引の構造を利用したソート処理の回避が可能だが、INDEX FAST FULL SCANは、ソート処理の回避ができない
• 索引ブロックを先頭エクステントから格納順にアクセスしていく方法なので返ってくる結果は索引キーの順にソートされていない

その他

HASH JOIN(INDEX JOIN)

▽特徴

• 複数の索引を結合アクセスするアクセスパス
• 結合した索引のみを参照するため高速

▽SQLの条件

• 索引が結合されている表から必要な列のみが索引に存在する列である場合にのみ可能

▽留意点

• 問合せに必要な列が索引に含まれている必要がある
• 2つの索引を統合した複合索引を作成したほうが、より性能面で効果的な場合が多い

AND-EQUAL

▽特徴

• 複数の列それぞれのB-tree Indexから取得したROWIDをマージし、テーブルからレコードを取得する

▽SQLの条件

• AND_EQUALヒントが必要

▽留意点

• インデックスが単一列の一意でないインデックスである必要がある
• 廃止予定の機能

実行例

事前準備

CREATE TABLE t1
(
   c1 NOT NULL
  ,c2 NOT NULL
  ,c3 NOT NULL
  ,c4 NOT NULL
  ,c5 NOT NULL
  ,c6 NOT NULL
)
AS
     SELECT ROWNUM
           ,ROWNUM
           ,ROWNUM
           ,ROWNUM
           ,ROWNUM
           ,ROWNUM
       FROM DUAL
  CONNECT BY LEVEL < 100;

CREATE INDEX t1_i1 ON t1 (c1, c2);

INDEX FULL SCAN

SQL> explain plan for
   2 SELECT /*+ index(t1 (c1,c2)) */ * FROM t1;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 1188580999

-----------------------------------------------------
| Id | Operation                           | Name   |
-----------------------------------------------------
|  0 | SELECT STATEMENT                    |        |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |
|  2 |   INDEX FULL SCAN                   | T1_I1  |
-----------------------------------------------------

<補足>

TABLE ACCESS BY INDEX ROWID BATCHED：
12cからの機能で「BATCH TABLE ACCESS BY ROWID」と呼ばれる動作。
表にBATCHアクセスするため、アクセス対象のデータブロック数が多い場合に効果的な機能

INDEX FULL SCAN (MIN/MAX)

SQL> explain plan for
   2 SELECT /*+ index(t1 (c1,c2)) */ MAX (c1) FROM t1;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 1743745495

--------------------------------------------
| Id | Operation                  | Name   |
--------------------------------------------
| 0  | SELECT STATEMENT           |        |
| 1  |  SORT AGGREGATE            |        |
| 2  |   INDEX FULL SCAN (MIN/MAX)| T1_I1  |
--------------------------------------------

INDEX FULL SCAN DESCENDING

SQL> explain plan for
   2 SELECT /*+ index_desc(t1 (c1,c2)) */ * FROM t1 ORDER BY c1 DESC, c2 DESC;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 4270362996

---------------------------------------------
| Id | Operation                   | Name   |
---------------------------------------------
| 0  | SELECT STATEMENT            |        |
| 1  |  TABLE ACCESS BY INDEX ROWID| T1     |
| 2  |   INDEX FULL SCAN DESCENDING| T1_I1  |
---------------------------------------------

INDEX RANGE SCAN

SQL> explain plan for
   2 SELECT /*+ index(t1 (c1,c2)) */ * FROM t1 WHERE c1 BETWEEN 3 AND 5;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 3320414027

-----------------------------------------------------
| Id | Operation                           | Name   |
-----------------------------------------------------
| 0  | SELECT STATEMENT                    |        |
| 1  |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |
| 2  |   INDEX RANGE SCAN                  | T1_I1  |
-----------------------------------------------------

INDEX RANGE SCAN DESCENDING

SQL> explain plan for
   2 SELECT /*+ index_desc(t1 (c1,c2)) */ * FROM t1 WHERE c1 BETWEEN 3 AND 5 ORDER BY c1 DESC;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 891765463

----------------------------------------------
| Id | Operation                    | Name   |
----------------------------------------------
| 0  | SELECT STATEMENT             |        |
| 1  |  TABLE ACCESS BY INDEX ROWID | T1     |
| 2  |   INDEX RANGE SCAN DESCENDING| T1_I1  |
----------------------------------------------

INDEX RANGE SCAN (MIN/MAX)

SQL> explain plan for
   2 SELECT /*+ index(t1 (c1,c2)) */ MIN (c2) FROM t1 WHERE c1 = 3 ORDER BY c1 DESC;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 3255648551

----------------------------------------------
| Id | Operation                   | Name    |
----------------------------------------------
| 0 | SELECT STATEMENT             |         |
| 1 |  SORT AGGREGATE              |         |
| 2 |   FIRST ROW                  |         |
| 3 |    INDEX RANGE SCAN (MIN/MAX)| T1_I1   |
----------------------------------------------

INDEX SKIP SCAN

SQL> explain plan for
   2 SELECT /*+ index_ss(t1 (c1,c2)) */ * FROM t1 WHERE c2 = 3 ORDER BY c1, c2;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 2886394002

---------------------------------------------
| Id | Operation                   | Name   |
---------------------------------------------
| 0  | SELECT STATEMENT            |        |
| 1  |  TABLE ACCESS BY INDEX ROWID| T1     |
| 2  |   INDEX SKIP SCAN           | T1_I1  |
---------------------------------------------

INDEX SKIP SCAN DESCENDING

SQL> explain plan for
   2 SELECT /*+ index_ss_desc(t1 (c1,c2)) */ * FROM t1 WHERE c2 = 3 ORDER BY c1 DESC, c2 DESC;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 216564213

---------------------------------------------
| Id | Operation                   | Name   |
---------------------------------------------
| 0  | SELECT STATEMENT            |        |
| 1  |  TABLE ACCESS BY INDEX ROWID| T1     |
| 2  |   INDEX SKIP SCAN DESCENDING| T1_I1  |
---------------------------------------------

INDEX UNIQUE SCAN

SQL> CREATE UNIQUE INDEX t1_i2 ON t1 (c2);

Index created.

SQL> explain plan for
   2 SELECT /*+ index(t1 (c2)) */ * FROM t1 WHERE c2 = 1;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 3045864492

---------------------------------------------
| Id | Operation                   | Name   |
---------------------------------------------
| 0  | SELECT STATEMENT            |        |
| 1  |  TABLE ACCESS BY INDEX ROWID| T1     |
| 2  |   INDEX UNIQUE SCAN         | T1_I2  |
---------------------------------------------

INDEX FAST FULL SCAN

SQL> explain plan for
   2 SELECT /*+ index_ffs(t1 (c1, c2)) */ c1, c2 FROM t1 WHERE c2 = 1;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 986441562

--------------------------------------
| Id | Operation            | Name   |
--------------------------------------
| 0  | SELECT STATEMENT     |        |
| 1  |  INDEX FAST FULL SCAN| T1_I1  |
--------------------------------------

INDEX SAMPLE FAST FULL SCAN

SQL> explain plan for
   2 SELECT /*+ index_ffs(t1 (c1, c2)) */ c1, c2 FROM t1 SAMPLE (5);

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 2304923920

---------------------------------------------
| Id | Operation                   | Name   |
---------------------------------------------
| 0  | SELECT STATEMENT            |        |
| 1  |  INDEX SAMPLE FAST FULL SCAN| T1_I1  |
---------------------------------------------

HASH JOIN(INDEX JOIN)

SQL> conn hr/hr@pdb1
Connected.
SQL> explain plan for
   2 SELECT e.first_name FROM hr.employees e WHERE e.manager_id >=100 AND e.last_name LIKE '%ran%';

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 3059678737

---------------------------------------------------
| Id | Operation               | Name             |
---------------------------------------------------
| 0  | SELECT STATEMENT        |                  |
| 1  |  VIEW                   | index$_join$_001 |
| 2  |   HASH JOIN             |                  |
| 3  |    INDEX RANGE SCAN     | EMP_MANAGER_IX   |
| 4  |    INDEX FAST FULL SCAN | EMP_NAME_IX      |
---------------------------------------------------

AND-EQUAL

SQL> explain plan for
   2 SELECT /*+ and_equal(e (manager_id) (job_id)) */
   3 employee_id
   4 ,first_name
   5 ,last_name
   6 ,email
   7 FROM hr.employees e
   8 WHERE e.manager_id = 124 AND e.job_id = 'SH_CLERK';

Explained.

SQL> select * from table(dbms_xplan.display(format=>'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 2196596023

------------------------------------------------------
| Id | Operation                   | Name            |
------------------------------------------------------
| 0  | SELECT STATEMENT            |                 |
| 1  |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |
| 2  |   AND-EQUAL                 |                 |
| 3  |    INDEX RANGE SCAN         | EMP_MANAGER_IX  |
| 4  |    INDEX RANGE SCAN         | EMP_JOB_IX      |
------------------------------------------------------

参考資料

Expert Oracle SQL 

Expert Oracle SQL: Optimization, Deployment, and Statistics

SQLパフォーマンス詳解

SQLパフォーマンス詳解

データベースパフォーマンスアップの教科書 基本原理編 

データベースパフォーマンスアップの教科書 基本原理編

パフォーマンス改善と事前対策に役立つ Oracle SQLチューニング 

パフォーマンス改善と事前対策に役立つ Oracle SQLチューニングSQLチューニング (DB SELECTION)

プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに

プログラマのためのSQL 第4版

SQL実践入門──高速でわかりやすいクエリの書き方 

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)