見出し画像

Oracleのオプティマイザのアクセスパス

この記事を読むとできるようになること

  • Oracleのオプティマイザのアクセスパスが何かを説明できるようになる

  • Oracleのオプティマイザがアクセスパスを選択した理由を理解できるようになる

  • 実行計画に出ているアクセスパスを見て、実行計画の良し悪しの判断、改善方法の検討ができるようになる

はじめに

こんにちは。SHIFTの小澤です。
SQLを実行すると、オプティマイザはSQLを解析し、複数の実行計画を作成し、その中から最もコストの低い実行計画を選択してデータベースに問い合わせを行います。
実行計画の中では表やインデックスにアクセスしてブロックを読み取りますが、アクセス方法にはいくつも方法があり、アクセス方法のことをアクセスパスと言います。

今回はアクセスパスについて見ていきます。

今回取り上げたアクセスパスはSQLチューニングを行う上で良くでてくるものですので是非おさえていただきたい内容です(今回取り上げたもの以外にもありますので少しずつおさえていっていただければと思います)。

全表スキャン(テーブルフルスキャン)

テーブルフルスキャンでは、HWMを下回るすべてのフォーマット済みブロックを順に読み取ります。
低いHWMの下のブロックは隣接しているため、一度に複数のブロックを読み取ることでスキャンを高速化できます。
(マルチブロック読み取り) マルチブロック読み取りのサイズはDB_FILE_MULTIBLOCK_READ_COUNT初期化パラメータで指定します。例えば4と指定すると1回のI/Oコールで最大4ブロックを読み取ります。

実際に見てみます。
(EMPLOYEESテーブルというのはOracle製品で提供されているデモ用のテーブルです。開発環境ではOracleをインストールする時に一緒にインストールすることが多いと思いますが、詳しくは別の記事で触れようと思います。)

SQL> select salary from hr.employees where salary > 4000;

<略>
64 rows selected.

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	cna25fzrh4xv1, child number 0
-------------------------------------
select salary from hr.employees where salary > 4000

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation	  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	      |       |       |     3 (100)|	      |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    65 |   260 |     3	(0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SALARY">4000)


18 rows selected.

テーブルフルスキャンをするとTABLE ACCESS FULLと出ます。
Salary列にインデックスが無いため、オプティマイザがテーブルフルスキャンを選択しました。
実行計画はDBMS_XPLAN.DISPLAY_CURSORで取得できます。 Exadataの場合は"TABLE ACCESS STORAGE FULL"のように"STORAGE"が付いたアクセスパス名で出ます。

ROWIDによる表アクセス

例えば以下のSQLを見てみます。

SQL> select * from hr.employees where department_id = 20;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         17-FEB-04
MK_MAN          13000                       100            20

        202 Pat                  Fay                       PFAY                      603.123.6666         17-AUG-05
MK_REP           6000                       201            20


SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  1nq4du390k3q6, child number 0
-------------------------------------
select * from hr.employees where department_id = 20

Plan hash value: 235881476

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |   138 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=20)


19 rows selected.

hr.employees表にあるemp_department_ixインデックスのレンジ・スキャンを使っています。
インデックスで取得したROWIDを使用し、employees表から対応する行のデータのみを取得しています。
"BATCHED"というのは複数のROWIDをまとめてアクセスする方法です。 1個1個アクセスするよりもI/O数を減らせます。
基本的に「ROWIDによる表アクセスの速さ>>テーブルフルスキャンの速さ」(ROWIDによる表アクセスの方が速い)。
例外的にテーブルフルスキャンが速いことはあり得るのですが、条件が揃わないとそうならないので基本的にはテーブルフルスキャンよりもインデックス・レンジ・スキャンで行を絞り込む方が速いと覚えておくほうが良いと思います。

索引一意スキャン(インデックス・ユニーク・スキャン)

索引一意スキャン(インデックス・ユニーク・スキャン)はROWIDを1件返す検索です。
CREATE UNIQUE INDEXで作った索引を等価演算子"="で検索する場合に実行できます。
索引一意スキャンでは、2つ目のレコードが無いことが分かっているため、最初のレコードが見つかりROWIDを1つ取得した時点で索引一意スキャンの処理が停止します。
(値がユニークなのですべて1件ずつしか無いので2件目を探す必要がない) 1件取得したROWIDを使って表のレコードを取得します。

以下の例を見てみます。

SQL> select * from sh.products where prod_id = 19;

<略>

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID	3ptq5tsd5vb3d, child number 0
-------------------------------------
select * from sh.products where prod_id = 19

Plan hash value: 1005976121

-------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  |	  |	  |	1 (100)|	  |
|   1 |  TABLE ACCESS BY INDEX ROWID| PRODUCTS	  |	1 |   173 |	1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX UNIQUE SCAN	    | PRODUCTS_PK |	1 |	  |	0   (0)|	  |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PROD_ID"=19)


19 rows selected.

products.prod_id列に主キーが存在し(主キーは一意キーの条件を満たしている)、WHERE句が等価演算子"="を使用しているため、オプティマイザが索引一意スキャンを選択しています。

索引レンジスキャン(インデックス・レンジ・スキャン)

索引レンジ・スキャン(インデックス・レンジ・スキャン)はROWIDを複数件返す検索です。 索引の先頭列を等価演算子"="か比較演算子"<"や">"で検索する場合に実行できます。
ANDを使っても良いです。 LIKEは先頭一致'ASD%'なら良いですが、部分一致'%ASD%'や後方一致'ASD%'だとレンジ・スキャンを実行できません。
索引はキー値でソート済みなので、ORDER BYでのSELECT実行時に索引を検索することでソート処理を省略することがあります。

以下の例を見てみます。

SQL> select * from hr.employees where department_id = 20 and salary > 1000;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         17-FEB-04
MK_MAN          13000                       100            20

        202 Pat                  Fay                       PFAY                      603.123.6666         17-AUG-05
MK_REP           6000                       201            20


SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  87pnju5fr4t1n, child number 0
-------------------------------------
select * from hr.employees where department_id = 20 and salary > 1000

Plan hash value: 235881476

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |   138 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SALARY">1000)
   2 - access("DEPARTMENT_ID"=20)


20 rows selected.

department_id列の索引レンジ・スキャンを実行し、employees表からレコードを取得した後、saraly > 1000フィルタを適用して結果を返しています。

別の例を見てみます。

SQL> select * from hr.employees where department_id < 20
  2  order by department_id desc;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17-SEP-03
AD_ASST          4400                       101            10


SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  bcfrwhfkh4wgv, child number 0
-------------------------------------
select * from hr.employees where department_id < 20 order by
department_id desc

Plan hash value: 2241259804

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     2 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |     2 |   138 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"<20)


20 rows selected.

sh.employees表のdepartment_id < 20のレコードをdepartment_id DESCでソートして返すように問い合わせています。
department_id列の索引レンジ・スキャンを実行しています。
department_idが20未満で最大のキー値を含む索引リーフ・ブロックを取得します。
次に、左右にリンクされたリーフ・ノードを左方向に進みます。
各索引エントリからROWIDを取得した後、ROWIDで指定されるレコードを取得します。

全索引スキャン(インデックス・フルスキャン)

全索引スキャン(インデックス・フルスキャン)は索引全体を順に読み取ります。 ルート・ブロックからブランチ・ブロックを左方向に読んでいきリーフ・ブロックに到達します。
(降順全索引スキャンなら右方向からリーフ・ブロックに到達する。以下同様に昇順スキャンの逆方向の進行になる) リーフ・ブロックを1ブロックずつ右方向に進みます(1ブロックずつ進むのは1ブロック読むまで右方向のリンク先がわからないため) レンジ・スキャンと同じ理由により索引を検索することでSELECT文のソート処理を省略することをオプティマイザが選択する場合があります。

例を見てみます。 hr.departments表のdepartment_idを昇順ソートで問い合わせます。

SQL> select department_id, department_name from hr.departments
  2  order by department_id;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           10 Administration
           20 Marketing
           30 Purchasing
           40 Human Resources
           50 Shipping
           60 IT
           70 Public Relations
           80 Sales
           90 Executive
          100 Finance
          110 Accounting

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          120 Treasury
          130 Corporate Tax
          140 Control And Credit
          150 Shareholder Services
          160 Benefits
          170 Manufacturing
          180 Construction
          190 Contracting
          200 Operations
          210 IT Support
          220 NOC

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          230 IT Helpdesk
          240 Government Sales
          250 Retail Sales
          260 Recruiting
          270 Payroll

27 rows selected.

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1pkucvqyy411, child number 0
-------------------------------------
select department_id, department_name from hr.departments order by
department_id

Plan hash value: 3145200496

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


15 rows selected.

department_id列の列の全索引スキャンを実行し、各索引エントリからROWIDを取得し、ROWIDで指定されるdepartments表のレコードを取得します。
索引はdepartment_idでソート済みのため、あらためてソートする必要はありません。
(もし、インデックスを利用せずdepartments表に直接アクセスした場合は別途ソート処理が必要です)

高速全索引スキャン(インデックス・ファスト・フルスキャン)

高速全索引スキャン(インデックス・ファスト・フルスキャン)では、索引ブロックをディスク上に存在している通りに読み取ります。 つまり、リーフ・ブロックの左右のリンクを読み取ってソート順に読み取るわけではないため、索引を読み取った時点でソートされていません。
SELECT文の結果を返すためにソートが必要な場合は別途ソート処理を実行する必要があります。

ただ、リーフ・ブロックの左右のリンクを読み取る必要がないため、マルチブロックI/Oを使えます(1回のI/Oで複数ブロックを取得できるためインデックス全体を「高速に」取得できます。ルート・ブロックとブランチ・ブロックの読み取りも行いません。)

初期化ファイルのDB_FILE_MULTIBLOCK_READ_COUNTパラメータにより、同時に読み取ることができるブロックの数を制御します。
高速全索引スキャンは、通常、フルテーブルスキャンよりも必要な物理I/Oが少なく、問い合わせをより速く実行できます。

高速全索引スキャンは、テーブルに対する問い合わせのすべての列がインデックスにあり、インデックスの先頭列がWHERE句の条件に含まれていない時に使われます。
(ヒント句INDEX _FFSが必要な場合があります)

例を見てみます。 EMPテーブルの列EMPNO、ENAME、DEPTNOに複合INDEXを張ってあります。

SQL> select table_name, index_name, column_name, column_position
  2  from user_ind_columns
  3* where table_name = 'EMP'

TABLE_NAME           INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- -------------------- ---------------
EMP                  PK_EMP               EMPNO                              1
EMP                  EMP_ID1              EMPNO                              1
EMP                  EMP_ID1              ENAME                              2
EMP                  EMP_ID1              DEPTNO                             3
EMP                  EMP_ID2              SAL                                1

EMP表のEMPNO、ENAME、DEPTNOを検索してみます。

SQL> select empno, ename, deptno
  2  from emp
  3  where deptno = 30;

     EMPNO ENAME	  DEPTNO
---------- ---------- ----------
      7499 ALLEN	      30
      7521 WARD 	      30
      7654 MARTIN	      30
      7698 BLAKE	      30
      7844 TURNER	      30
      7900 JAMES	      30

6 rows selected.

実行計画を確認すると、Skip Scanが選択されています。

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	gpa3g443614nr, child number 0
-------------------------------------
select empno, ename, deptno from emp where deptno = 30

Plan hash value: 1294623169

----------------------------------------------------------------------------
| Id  | Operation	 | Name    | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	   |	   |	   |	 1 (100)|	   |
|*  1 |  INDEX SKIP SCAN | EMP_ID1 |	 6 |	78 |	 1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=30)
       filter("DEPTNO"=30)


19 rows selected.

INDEX_FFSヒントを付けて実行してみます。

SQL> select /*+ INDEX_FFS(emp EMP_ID1) */empno, ename, deptno
  2  from emp
  3  where deptno = 30;

     EMPNO ENAME	  DEPTNO
---------- ---------- ----------
      7499 ALLEN	      30
      7521 WARD 	      30
      7654 MARTIN	      30
      7698 BLAKE	      30
      7844 TURNER	      30
      7900 JAMES	      30

6 rows selected.

今度はFast Full Scanを選択しました。

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	42kgrr30bph3b, child number 0
-------------------------------------
select /*+ INDEX_FFS(emp EMP_ID1) */empno, ename, deptno from emp where
deptno = 30

Plan hash value: 3613052774

--------------------------------------------------------------------------------
| Id  | Operation	     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	       |       |       |     2 (100)|	       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  1 |  INDEX FAST FULL SCAN| EMP_ID1 |     6 |    78 |     2	 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=30)


19 rows selected.

SQL文のすべての列がインデックスに含まれているため、高速全索引スキャンを利用できました。
高速全索引スキャンは、インデックス付き複合キー列のみが照会される結合の時にも実行されます。
高速全索引スキャンの代わりにインデックス・スキップ・スキャンを実行する場合があります。

別の例を見てみます。

SQL> select /*+ INDEX_FFS(departments dept_id_pk) */ count(*) from hr.departments;

  COUNT(*)
----------
        27

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  bpufsjvzhszc5, child number 0
-------------------------------------
select /*+ INDEX_FFS(departments dept_id_pk) */ count(*) from
hr.departments

Plan hash value: 2315979460

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |       |     2 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| DEPT_ID_PK |    27 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


15 rows selected.

department_id列の主キーのエントリ数がdepartments表のレコード数と一致することが分かっているため高速全索引スキャンを実行してdepartments表の件数を取得しています。
(もし、インデックスを利用せずdepartments表に直接アクセスして件数を求める場合は読み取りブロック数が多くなります)

索引スキップ・スキャン(インデックス・スキップ・スキャン)

一般的に、表のブロックをスキャンするよりも、索引ブロックをスキャンする方が高速に実行できます。
(読み取るブロック数を少なくできます) インデックス・スキップ・スキャンを使用すると、先頭の列がWHERE句にない場合でもオプティマイザは複合INDEXを使用できます。
(WHERE句に関係ない先頭列の索引ブロックを「スキップ」します) インデックス・スキップ・スキャンを使うと、通常、全索引スキャンよりも高速でI/Oが少なくなります。

例を見てみます。

SQL> set timing on
SQL> set autotrace on
SQL> select empno, ename, deptno
  2  from emp
  3  where deptno = 30;

EMPNO ENAME       DEPTNO
---------- ---------- ----------
      7499 ALLEN              30
      7521 WARD               30
      7654 MARTIN             30
      7698 BLAKE              30
      7844 TURNER             30
      7900 JAMES              30

6 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1294623169

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     6 |    78 |     1   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | EMP_ID1 |     6 |    78 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=30)
       filter("DEPTNO"=30)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        844  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

INDEX_FFSヒントで実行してみます。

SQL> select /*+ INDEX_FFS(emp EMP_ID1) */empno, ename, deptno
  2  from emp
  3  where deptno = 30;

EMPNO ENAME       DEPTNO
---------- ---------- ----------
      7499 ALLEN              30
      7521 WARD               30
      7654 MARTIN             30
      7698 BLAKE              30
      7844 TURNER             30
      7900 JAMES              30

6 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3613052774

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     6 |    78 |     2   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| EMP_ID1 |     6 |    78 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=30)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        844  bytes sent via SQL*Net to client
        445  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

インデックス・スキップ・スキャンを使用した場合は論理読み込み(consistent gets)が2でした。
高速全索引スキャンを使用した場合は論理読み込みは5でした。
オプティマイザにSkip Scanを選択させるにはINDEX_SSヒントが必要な場合があります。

索引結合スキャン

索引結合スキャンでは、複数の索引をスキャンし、取得したROWIDでハッシュ結合を実行して行を返します。

例を見てみます。

hr.employees表には(last_name, first_name)でのコンポジット索引と、email列での索引が貼ってあります。
データベースはemail列の索引のすべてのROWIDを取得し、コンポジット索引でAで始まるlast_nameのROWIDを取得します。
ハッシュ結合を使用して、ROWIDの2つのセットから一致するものを探します。

SQL> select /*+ INDEX_JOIN(employees) */ last_name, email
  2  from hr.employees where last_name like 'A%';

LAST_NAME                 EMAIL
------------------------- -------------------------
Austin                    DAUSTIN
Abel                      EABEL
Atkinson                  MATKINSO
Ande                      SANDE

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5jya2mvm7dw43, child number 0
-------------------------------------
select /*+ INDEX_JOIN(employees) */ last_name, email from hr.employees
where last_name like 'A%'

Plan hash value: 325493464

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |       |       |     2 (100)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                  | index$_join$_001 |     3 |    48 |     2   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN    | EMP_NAME_IX      |     3 |    48 |     1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| EMP_EMAIL_UK     |     3 |    48 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LAST_NAME" LIKE 'A%')
   2 - access(ROWID=ROWID)

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
   3 - access("LAST_NAME" LIKE 'A%')


24 rows selected.


【このSHIFT公式ブロガーの最新記事】


執筆者プロフィール:小澤 雅弘
DBMSベンダー、ISPなどでDB関連のプロジェクトを20数年経験。得意領域はDBのパフォーマンスチューニング。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて(コーポレートサイト)
https://www.shiftinc.jp/

SHIFTのサービスについて(サービスサイト)
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/