select count(*)するときのrownum指定による性能差

はじめに

ある条件に合致するレコードがあるかないかを調べるとき、select count(*) from table where 条件;とするのと、select count(*) from table where 条件 and rownum <= 1;
とするのではどれだけ性能差があるのか調べてみました。

ざっくり言うと

rownumを指定したほうが速い
500万レコードでざっと3秒くらい違う
性能差が生まれる原因は読み込むブロック数にある

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

posted with amazlet at 15.05.15

加藤祥平中島益次郎
翔泳社
売り上げランキング: 104,153

Amazon.co.jpで詳細を見る

環境

Windows Server 2008 R2 SP1
Oracle 11g(11.2.0.1.0)

テストデータを用意する

以下のようなテーブルを用意し、テストデータを500万レコード登録する。

truncate table table01;
set timing on
insert /*+ APPEND */ into table01 nologging (
id, name, furigana, seibetsu, birthday) 
  (select 
  lpad(rownum, 8, '0'),
  concat('テスト　太郎', TO_MULTI_BYTE(rownum)),
  concat('てすと　たろう', TO_MULTI_BYTE(rownum)),
  CASE MOD(rownum, 2)
    WHEN 0 THEN '男'
    ELSE '女'
  END,
  TO_CHAR(TO_DATE('20130101','YYYYMMDD')
    + FLOOR(DBMS_RANDOM.VALUE(0, 365)), 'YYYYMMDD')
  from (select 0 from all_catalog where rownum <= 5000),
       (select 0 from all_catalog where rownum <= 1000)
  );
commit;

検証方法

ある条件で条件にマッチするレコードがあるかないかを調べたい。
以下の２つのSQLの実行にかかる時間、コストを比較する。

a. select count(*) from table01 where seibetsu = '男';
b. select count(*) from table01 where seibetsu = '男' and rownum <= 1;

実行時間

f:id:replication:20150513081359p:plain

試行回数	rownum指定なし(秒)	rownum指定あり(秒)
1st	2.09	0.07
2st	2.93	0.25
3st	3.18	0.14
avg	2.73	0.15

クエリaは250万レコード返却しますが、クエリbは1レコードのみ返却します。
rownum指定をして返却される件数を絞ったほうが速いことが分かりました。

コスト

クエリa

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     3 | 20762   (1)| 00:04:10 |
|   1 |  SORT AGGREGATE    |         |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE01 |  2191K|  6418K| 20762   (1)| 00:04:10 |
------------------------------------------------------------------------------

統計
----------------------------------------------------
          4  recursive calls
          0  db block gets
      76021  consistent gets
      75943  physical reads
          0  redo size
        446  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

クエリb

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |     3 | 20762   (1)| 00:04:10 |
|   1 |  SORT AGGREGATE     |         |     1 |     3 |            |          |
|*  2 |   COUNT STOPKEY     |         |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| TABLE01 |  2191K|  6418K| 20762   (1)| 00:04:10 |
-------------------------------------------------------------------------------

統計
-----------------------------------------------------
          7  recursive calls
          0  db block gets
        150  consistent gets
         13  physical reads
          0  redo size
        445  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

autotraceした結果、クエリの探索コストはどちらも変わりませんでした。
性能差が生まれた原因は、consistent getsとphysical readsにあると思われます。