COUNT(*)とCOUNT(1)の性能検証・2回目

こじ

7年前

前回から引き続き、SELECT COUNT(*)とSELECT COUNT(1)の処理速度検証を行いました。

今回はレコード件数を100万件、500万件、1,000万件で各3回ずつ計測しました。

TB_MAINテーブルの定義

DESC TB_MAIN;
 名前      NULL?    型
 --------- -------- ----------------------------
 ID        NOT NULL NUMBER(10)
 NAME               VARCHAR2(12 CHAR)

SELECT COUNT(*)　/　100万件

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
    100000

経過: 00:00:00.36

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
    100000

経過: 00:00:00.72

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
    100000

経過: 00:00:00.57
SELECT COUNT(*) FROM TB_MAIN;

実行計画
----------------------------------------------------------
Plan hash value: 3832545512

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    68   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007657 | 91707 |    68   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SELECT COUNT(1)　/　100万件

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
    100000

経過: 00:00:00.41

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
    100000

経過: 00:00:00.77

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
    100000

経過: 00:00:00.56

SELECT COUNT(1) FROM TB_MAIN;

実行計画
----------------------------------------------------------
Plan hash value: 3832545512

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    68   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007657 | 91707 |    68   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SELECT COUNT(*)　/　500万件

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
    500000

経過: 00:00:00.98

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
    500000

経過: 00:00:00.82

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
    500000

経過: 00:00:01.03

SELECT COUNT(*) FROM TB_MAIN;

実行計画
----------------------------------------------------------
Plan hash value: 3832545512

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |   272   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007657 |   599K|   272   (0)| 00:00:04 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SELECT COUNT(1)　/　500万件

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
    500000

経過: 00:00:00.76

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
    500000

経過: 00:00:01.27

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
    500000

経過: 00:00:01.01

SELECT COUNT(1) FROM TB_MAIN;

実行計画
----------------------------------------------------------
Plan hash value: 3832545512

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |   272   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007657 |   599K|   272   (0)| 00:00:04 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SELECT COUNT(*)　/　1,000万件

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
   1000000

経過: 00:00:01.01

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
   1000000

経過: 00:00:00.90

SELECT COUNT(*) FROM TB_MAIN;

  COUNT(*)
----------
   1000000

経過: 00:00:01.03

SELECT COUNT(*) FROM TB_MAIN;

実行計画
----------------------------------------------------------
Plan hash value: 3832545512

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |   511   (0)| 00:00:07 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007657 |  1055K|   511   (0)| 00:00:07 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SELECT COUNT(1)　/　1,000万件

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
   1000000

経過: 00:00:00.97

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
   1000000

経過: 00:00:00.96

SELECT COUNT(1) FROM TB_MAIN;

  COUNT(1)
----------
   1000000

経過: 00:00:00.99

SELECT COUNT(1) FROM TB_MAIN;

実行計画
----------------------------------------------------------
Plan hash value: 3832545512

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |   511   (0)| 00:00:07 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007657 |  1055K|   511   (0)| 00:00:07 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SELECT COUNT(*)、COUNT(1)の処理速度検証結果

100万件の測定結果

	SELECT COUNT(*)	SELECT COUNT(1)
1回目	0.36秒	0.41秒
2回目	0.72秒	0.77秒
3回目	0.57秒	0.56秒
平均	0.55秒	0.58秒

500万件の測定結果

回数/平均	SELECT COUNT(*)	SELECT COUNT(1)
1回目	0.98秒	0.76秒
2回目	0.82秒	1.27秒
3回目	1.03秒	1.01秒
平均	0.94秒	1.01秒

1,000万件の測定結果

回数/平均	SELECT COUNT(*)	SELECT COUNT(1)
1回目	1.01秒	0.97秒
2回目	0.90秒	0.96秒
3回目	1.08秒	0.99秒
平均	1.00秒	0.97秒

実行計画のコストはレコード件数に比例して増加しますが、SELECT COUNT(*)とSELECT COUNT(1)で違いはありません。

統計に関しては『recursive calls』と『consistent gets（読み込まれたブロック数）』に関しては違いがでます。しかし、処理速度的にはどちらもほとんどかわりません。

次回はカラム数を増やして検証していきます。

以上です。

TB_MAINテーブルの定義

SELECT COUNT(*) / 100万件

SELECT COUNT(1) / 100万件

SELECT COUNT(*) / 500万件

SELECT COUNT(1) / 500万件

SELECT COUNT(*) / 1,000万件

SELECT COUNT(1) / 1,000万件