1. /
  2. /
  3. COUNT(*)とCOUNT(1)の性能検証・2回目
SQL

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

  • このエントリーをはてなブックマークに追加
  • by こじ
  • 0

前回から引き続き、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（読み込まれたブロック数）』に関しては違いがでます。しかし、処理速度的にはどちらもほとんどかわりません。

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

以上です。

  • このエントリーをはてなブックマークに追加

コメントを残す

*