count(*)与count(一)的区别有多大
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)
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>
其实：两个并没有多大差别！使用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>
4、使用count(1)的结果：
SQL> analyze table table_name compute statistics;
Table analyzed.
Elapsed: 00:00:02.89
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:04.95
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
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>
由此可见，对表作分析之后，使用count(1)要比count(*)的用时少些！
而且：表分析前后：count(*)分析后比count(*)分析前用时多了；count(1)分析后比count(1)分析前用时少了；对于提高性能来说，首先对表作分析，然后再使用count(1)就会省更多的时间。
后话：但是当表的数据量再大些时：
SQL> select count(*) from table_name;
COUNT(*)
----------
37054
5、使用count(*)的结果：
SQL> alter session set nls_language = "American";
Session altered.
SQL> set timing on;
SQL> set autotrace on;
SQL> analyze table table_name compute statistics;
Table analyzed.
Elapsed: 00:00:28.28
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 187
317994 2
Elapsed: 00:00:05.98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1844 Bytes=
7376)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=457 Card=1844 Bytes=7376)
3 2 TABLE ACCESS (FULL) OF 'table_name' (Cost=416 Card=37
054 Bytes=148216)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4315 consistent gets
0 physical reads
0 redo size
254 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)
2 rows processed
SQL>
6、使用count(1)的结果：
SQL> alter session set nls_language = "American";
Session altered.
SQL> set timing on;
SQL> set autotrace on;
SQL> analyze table table_name compute statistics;
Table analyzed.
Elapsed: 00:00:26.57
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 187
317994 2
Elapsed: 00:00:06.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1844 Bytes=
7376)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=457 Card=1844 Bytes=7376)
3 2 TABLE ACCESS (FULL) OF 'table_name' (Cost=416 Card=37
054 Bytes=148216)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4315 consistent gets
0 physical reads
0 redo size
254 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)
2 rows processed
SQL>
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！
从执行计划来看，count(1)和count(*)的效果是一样的。
但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
这个也与表的记录数多少有关！如果1w以外的数据量，做过表分析之后，反而count(1)的用时比count(*)多了。
另外，当数据量达到10w多的时候，使用count(1)要比使用count(*)的用时稍微少点！
因此：count(1)和count(*)基本没有差别！
sql调优，主要是考虑降低：consistent gets和physical reads的数量。
【编写于 2009-01-20】
数据库表的记录数为：
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)
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>
其实：两个并没有多大差别！使用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>
4、使用count(1)的结果：
SQL> analyze table table_name compute statistics;
Table analyzed.
Elapsed: 00:00:02.89
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:04.95
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
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>
由此可见，对表作分析之后，使用count(1)要比count(*)的用时少些！
而且：表分析前后：count(*)分析后比count(*)分析前用时多了；count(1)分析后比count(1)分析前用时少了；对于提高性能来说，首先对表作分析，然后再使用count(1)就会省更多的时间。
后话：但是当表的数据量再大些时：
SQL> select count(*) from table_name;
COUNT(*)
----------
37054
5、使用count(*)的结果：
SQL> alter session set nls_language = "American";
Session altered.
SQL> set timing on;
SQL> set autotrace on;
SQL> analyze table table_name compute statistics;
Table analyzed.
Elapsed: 00:00:28.28
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 187
317994 2
Elapsed: 00:00:05.98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1844 Bytes=
7376)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=457 Card=1844 Bytes=7376)
3 2 TABLE ACCESS (FULL) OF 'table_name' (Cost=416 Card=37
054 Bytes=148216)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4315 consistent gets
0 physical reads
0 redo size
254 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)
2 rows processed
SQL>
6、使用count(1)的结果：
SQL> alter session set nls_language = "American";
Session altered.
SQL> set timing on;
SQL> set autotrace on;
SQL> analyze table table_name compute statistics;
Table analyzed.
Elapsed: 00:00:26.57
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 187
317994 2
Elapsed: 00:00:06.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1844 Bytes=
7376)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=457 Card=1844 Bytes=7376)
3 2 TABLE ACCESS (FULL) OF 'table_name' (Cost=416 Card=37
054 Bytes=148216)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4315 consistent gets
0 physical reads
0 redo size
254 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)
2 rows processed
SQL>
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！
从执行计划来看，count(1)和count(*)的效果是一样的。
但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
这个也与表的记录数多少有关！如果1w以外的数据量，做过表分析之后，反而count(1)的用时比count(*)多了。
另外，当数据量达到10w多的时候，使用count(1)要比使用count(*)的用时稍微少点！
因此：count(1)和count(*)基本没有差别！
sql调优，主要是考虑降低：consistent gets和physical reads的数量。
【编写于 2009-01-20】