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 1EMP表の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 processedINDEX_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/