SQL

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

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

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

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

以上です。

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

コメント

コメントを残す

*