【Oracle】データ・アクセス方式の違いによる性能差

はじめに

Oracleには、6つのデータ・アクセス方式があります。

全表スキャン(TABLE ACCESS FULL)
索引一意スキャン(INDEX UNIQUE SCAN)
索引レンジスキャン(INDEX RANGE SCAN)
索引フルスキャン(INDEX FULL SCAN)
索引スキップスキャン(INDEX SIKP SCAN)
索引高速フルスキャン(INDEX FAST FULL SCAN)

これらの性能差を比較してみたいと思います。

用意したテーブル

create table table01
(
 id char(8),           --ID
 name varchar(50),     --名前
 furigana varchar(50), --ふりがな
 seibetsu varchar(3),  --性別
 birthday char(8),     --生年月日
 primary key( id )
);
CREATE INDEX test_idx1 ON hoge.table01(birthday) TABLESPACE USERS;
CREATE INDEX test_idx2 ON hoge.table01(seibetsu, birthday) TABLESPACE USERS;

このテーブルに1千万件のレコードを挿入します。
性別の男女比は50%ずつ、生年月日は365日の期間でランダムです。

全表スキャン(TABLE ACCESS FULL)

インデックスの貼られていないカラムをキーに検索します。

set timing on
set autotrace on
select count(*) from table01 where name = 'テスト　太郎１０００００００';

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    40 | 41738   (1)| 00:08:21 |
|   1 |  SORT AGGREGATE    |         |     1 |    40 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE01 |     1 |    40 | 41738   (1)| 00:08:21 |
------------------------------------------------------------------------------

索引一意スキャン(INDEX UNIQUE SCAN)

主キーで検索します。

set timing on
set autotrace on
select count(*) from table01 where id = '10000000';

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |     9 |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0030122 |     1 |     9 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

索引レンジスキャン(INDEX RANGE SCAN)

主キー以外のインデックスを使用して検索します。

set timing on
set autotrace on
select name from table01 where birthday = '20131201';

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     9 |    79   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_IDX1 | 27397 |   240K|    79   (0)| 00:00:01 |
-------------------------------------------------------------------------------

索引フルスキャン(INDEX FULL SCAN)

再現できませんでした。

索引スキップスキャン(INDEX SIKP SCAN)

複合インデックスの1列目の条件が無効（notを使用している）で2列目が有効な場合

set timing on
set autotrace on
select count(*) from table01 where not seibetsu = '男' and birthday = '20131201';

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    13 |    94   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE  |           |     1 |    13 |            |          |
|*  2 |   INDEX SKIP SCAN| TEST_IDX2 | 13436 |   170K|    94   (0)| 00:00:02 |
------------------------------------------------------------------------------

索引高速フルスキャン(INDEX FAST FULL SCAN)

問い合わせに必要なすべての列がインデックスに含まれていて、インデックスキーの1つ以上の列にnot null制約が指定されている場合

set timing on
set autotrace on
select count(*) from table01;

------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |  6530   (2)| 00:01:19 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C0030122 |    10M|  6530   (2)| 00:01:19 |
------------------------------------------------------------------------------