小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


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

はじめに

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

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

環境
  • 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;
検証方法

ある条件で条件にマッチするレコードがあるかないかを調べたい。
以下の2つの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にあると思われます。

consistent getsとphysical reads

consistent getsは、ブロックの読取り一貫性が要求された回数。
physical readsは、ディスクから読み込まれたデータ・ブロックの合計数。
どちらも値が小さいほうが高速に動作します。

consistent gets physical reads
rownum指定なし 76021 75943
rownum指定あり 150 13
まとめ

select count(*)文によってレコードの件数を正確に取得したい場合を除き、単純にレコードがあるか否かを調べる場合は、rownumを指定したほうが速度的に有利です。