count(*)与count(1)的区别有多大？

文章由LinuxBoy分享于2019-03-30 02:03:51热评（216）

count(*)与count(1)的区别有多大？

数据库表的记录数为：

SQL> select count(*) from table_name t;

COUNT(*)
----------
6873

1、使用count(*)的统计结果：

SQL> alter session set nls_language = "American";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> select a.document_id,count(*) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID COUNT(*)
----------- ----------
0 71

Elapsed: 00:00:05.20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=339 Bytes=16
95)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=90 Card=339 Bytes=1695)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=78 Card=6828
           Bytes=34140)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        837 consistent gets
          0 physical reads
          0 redo size
        230 bytes sent via SQL*Net to client
        242 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>
2、使用count(1)的统计结果：

SQL> alter session set nls_language = "American";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> select a.document_id,count(1) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID COUNT(1)
----------- ----------
0 71

Elapsed: 00:00:05.57

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=339 Bytes=16
95)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=90 Card=339 Bytes=1695)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=78 Card=6828
           Bytes=34140)

SQL>

其实：两个并没有多大差别！使用count(1)要比count(*)的用时多些！

对数据库表作分析之后的比较：

3、使用count(*)的结果：

SQL> analyze table table_name compute statistics;

Table analyzed.

Elapsed: 00:00:02.92
SQL> select a.document_id,count(*) from table_name a group by a.document_id having count(a.document_i

DOCUMENT_ID COUNT(*)
----------- ----------
0 71

Elapsed: 00:00:05.43

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=341 Bytes=13
64)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=93 Card=341 Bytes=1364)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=82 Card=6873
           Bytes=27492)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        837 consistent gets
          0 physical reads
          0 redo size
        231 bytes sent via SQL*Net to client
        241 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

推荐文章：

count(*)与count(1)的区别有多大？