Oracleの複合インデックスについて

2023年2月8日 14:11

はじめに

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

こんにちは。SHIFTの小澤です。

Oracleの複合インデックスとはどういうものかを説明できるようになる
Oracleの複合インデックスを使って性能改善ができるようになる

複合インデックスの基本的な考え方

テーブルの複数の列に対して一つのインデックスを作成したものを「複合インデックス」、「連結インデックス」などと言います。インデックスに指定する列の順番には意味があり、基本的には先頭列をカーディナリティが高くてWhere句で検索する列にします。

複合インデックスが作成され、B-Tree(Balanced Treeと言って性能を良くするためのインデックスのブロック構造です)が物理ディスクにインデックス・ブロックとして配置される時に先頭列から検索しやすいように並んでいくのですが、先頭列をカーディナリティが高い列にしておけば、Where句で検索する時に性能が良くなります(取得するブロック数を一気に削れるようになる)。

元々はWhere句に複合インデックスの先頭列が含まれていない場合はそもそもそのインデックスを利用することはなかったのですが、その後インデックス・スキップ・スキャンという機能が出てきて、先頭列以外の検索でも使えるようになりました。

インデックス・スキップ・スキャンによる検索で良いケースもあるのですが、スキップ・スキャンの場合は先頭列以外でレンジスキャンを繰り返すような動作になるので、できれば最初から1回のレンジ・スキャンで処理できるようにインデックスを作っておいたほうが良いということになります。
ですので今でも基本的にはカーディナリティが高い列を複合インデックスの先頭にすると覚えておいていただければと思います。

複合インデックスを試してみる

Oracleを理解するにはやはりやってみるのが大事ということで実際に試してみます。

EMPテーブルをコピーしてEMP_TESTを作り、そこにEMPNO、ENAME、DEPTNOの順で複合INDEXを作ります。 (EMPテーブルというのはOracle製品で提供されているデモ用のテーブルです。

開発環境ではOracleをインストールする時に一緒にインストールすることが多いと思いますが、詳しくは別の記事で触れようと思います。)

SQL> create table emp_test as select * from emp;

Table created.

SQL> create index emp_test_n1 on emp_test(empno, ename, deptno);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N1	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N1			1 EMPNO
EMP_TEST	     EMP_TEST_N1			2 ENAME
EMP_TEST	     EMP_TEST_N1			3 DEPTNO

Where句で2番目の列を指定して検索してみます。Where句で1番目のEMPNOを指定しない限り、Oracleはこの索引(EMP_TEST_N1)をレンジスキャンでは使用しないのですが、今回はスキップスキャンが使えると判断されてスキップスキャンが実行されています。

SQL> select job, empno
  2  from emp_test
  3  where ename = 'SCOTT';

JOB	       EMPNO
--------- ----------
ANALYST 	7788

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	dhum194xf9ps0, child number 0
-------------------------------------
select job, empno from emp_test where ename = 'SCOTT'

Plan hash value: 3252221418

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX SKIP SCAN		    | EMP_TEST_N1 |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - access("ENAME"='SCOTT')
       filter("ENAME"='SCOTT')


20 rows selected.

ENAMEはインデックスの先頭列ではないので、オプティマイザがインデックスを使用しない選択をすることもあります。

今回は、スキップスキャンを使用するのが良いとオプティマイザが判断して、EMPNOがWhere句に指定されていないのにも関わらずEMP_TEST_N1を利用しました。

オプティマイザの他の選択肢としては、インデックス・ファスト・フルスキャンやテーブル・フルスキャンもあるので状況次第でそれらが選択されることもあり得ます。

同じように、インデックスの3番目の列をWhere句で指定した場合も見てみます。

SQL> select job, empno
  2  from emp_test
  3  where deptno = 30;

JOB	       EMPNO
--------- ----------
SALESMAN	7499
SALESMAN	7521
SALESMAN	7654
MANAGER 	7698
SALESMAN	7844
CLERK		7900

6 rows selected.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	bqzdnd33wcn8a, child number 0
-------------------------------------
select job, empno from emp_test where deptno = 30

Plan hash value: 3252221418

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX SKIP SCAN		    | EMP_TEST_N1 |	5 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

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


20 rows selected.

今回は、Where句ではインデックスの3番目の列を指定しています。
オプティマイザは、インデックスのスキップ・スキャン、インデックス・ファスト・フルスキャン、テーブル・フルスキャンの選択肢がありましたが、結果としてインデックス・スキップ・スキャンを選択しました。

インデックスを作成することで、SQLを実行する際の選択肢をより多くデータベースに与えることができ、うまくいけば性能が向上が可能です。
この時、ユーザが実行するSQL自体を特に変更しなくても、オプティマイザがインデックスを認識し、それぞれの選択肢についての見積もりCOSTを計算し、その値を基に採用する実行計画を決定します。

次に、インデックスの一部を使用する例を見てみます。先行列のEMPNOがWhere句の条件として使用されていて、Oracleがインデックスの最初の列を使用できるようになっています。
EMPNOとENAMEがWhere句に含まれている場合は、インデックスの先頭列と2番目の列を使用できます。
EMPNO、ENAME、DEPTNO全てがWhere句に含まれている場合は、インデックスの全ての列を使用できます。

次の例では、EMPNOの条件でインデックスの最初の列を使用します。

SQL> select job, empno
  2  from emp_test
  3  where empno = 7788;

JOB	       EMPNO
--------- ----------
ANALYST 	7788

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	gkph5r7abdjc8, child number 0
-------------------------------------
select job, empno from emp_test where empno = 7788

Plan hash value: 3630135763

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

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

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

   2 - access("EMPNO"=7788)


19 rows selected.

インデックス・スキャンで代表的なものと言えば、ユニーク・スキャンとレンジ・スキャンです。ユニーク・スキャンでは、Oracleはインデックスの値は一意であることを認識して処理します(値は必ず1個だけのため、インデックスを調べて値が1個見つかった時点で他の値を探しに行く必要はありません)。

レンジ・スキャンでは、OracleはSQLの条件に従ってインデックスから複数の値を取得します。ユニーク・インデックスを作成するには、インデックスの作成時に"create unique index"とします。

複合インデックスの一部の列を使う場合はレンジ・スキャンになります。

SQL> alter index emp_test_n1 invisible;

Index altered.

SQL> create unique index emp_test_n2 on emp_test(empno, ename, deptno);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N1	  NORMAL		NONUNIQUE VALID    INVISIBLE
EMP_TEST	     EMP_TEST_N2	  NORMAL		UNIQUE	  VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N1			1 EMPNO
EMP_TEST	     EMP_TEST_N1			2 ENAME
EMP_TEST	     EMP_TEST_N1			3 DEPTNO
EMP_TEST	     EMP_TEST_N2			1 EMPNO
EMP_TEST	     EMP_TEST_N2			2 ENAME
EMP_TEST	     EMP_TEST_N2			3 DEPTNO

6 rows selected.

SQL> select job, empno
  2  from emp_test
  3  where empno = 7788;

JOB	       EMPNO
--------- ----------
ANALYST 	7788

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	gkph5r7abdjc8, child number 0
-------------------------------------
select job, empno from emp_test where empno = 7788

Plan hash value: 2057995647

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

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

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

   2 - access("EMPNO"=7788)


19 rows selected.

単一列インデックスの場合はユニーク・スキャンになります。

SQL> create unique index emp_test_n3 on emp_test(empno);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N1	  NORMAL		NONUNIQUE VALID    INVISIBLE
EMP_TEST	     EMP_TEST_N2	  NORMAL		UNIQUE	  VALID    VISIBLE
EMP_TEST	     EMP_TEST_N3	  NORMAL		UNIQUE	  VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N1			1 EMPNO
EMP_TEST	     EMP_TEST_N1			2 ENAME
EMP_TEST	     EMP_TEST_N1			3 DEPTNO
EMP_TEST	     EMP_TEST_N2			1 EMPNO
EMP_TEST	     EMP_TEST_N2			2 ENAME
EMP_TEST	     EMP_TEST_N2			3 DEPTNO
EMP_TEST	     EMP_TEST_N3			1 EMPNO

7 rows selected.

SQL> select job, empno
  2  from emp_test
  3  where empno = 7788;

JOB	       EMPNO
--------- ----------
ANALYST 	7788

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	gkph5r7abdjc8, child number 0
-------------------------------------
select job, empno from emp_test where empno = 7788

Plan hash value: 1970880658

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

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

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

   2 - access("EMPNO"=7788)


19 rows selected.

主キーまたはユニーク制約を作成すると、指定した列に基づいてOracleが自動的にユニーク・インデックスを作成します。

複数列の主キーを作成すると、主キーの作成時に指定した順序と同じ順序の複合インデックスが作成されます。

【この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/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。