ITPub博客

首页 > Linux操作系统 > Linux操作系统 > count(*)与count(1)有区别吗?

count(*)与count(1)有区别吗?

原创 Linux操作系统 作者:lsq_008 时间:2009-03-21 10:19:54 0 删除 编辑

看到别人使用count(1)来统计记录数,据说是资源消耗少,今天做了个测试:

先看count(*):

[oracle@single bdump]$ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Mar 21 10:21:21 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

idle>startup
ORACLE instance started.

Total System Global Area  120017880 bytes
Fixed Size                   451544 bytes
Variable Size              67108864 bytes
Database Buffers           50331648 bytes
Redo Buffers                2125824 bytes
Database mounted.
Database opened.
idle>set autot on
idle>select count(*) from scott.test;

  COUNT(*)
----------
    282912


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST'

 


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


再看count(1):

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Mar 21 10:22:29 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

idle>startup
ORACLE instance started.

Total System Global Area  120017880 bytes
Fixed Size                   451544 bytes
Variable Size              67108864 bytes
Database Buffers           50331648 bytes
Redo Buffers                2125824 bytes
Database mounted.
Database opened.
idle>set autot on
idle>select count(1) from scott.test;

  COUNT(1)
----------
    282912


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST'

 


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

二者的资源消耗是一致的,没看出有多大区别。在tom的网站上,有这样一段:

You Asked

What is the difference between count(1) and count(*) in a sql query
eg.
select count(1) from emp;
   and
select count(*) from emp;

 


 

and we said...

nothing, they are the same, incur the same amount of work -- do the same thing, take the 
same amount of resources.


You can see this via:

ops$tkyte@ORA817.US.ORACLE.COM> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from all_objects;

  COUNT(*)
----------
     27044

ops$tkyte@ORA817.US.ORACLE.COM> select count(1) from all_objects
  2  /

  COUNT(1)
----------
     27044


and the tkprof will show:



select count(*)
from
 all_objects


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.56       5.56          0     234998          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.58       5.58          0     234998          4           1

select count(1)
from
 all_objects


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.46       5.47          0     234998          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.48       5.49          0     234998          4           1

Same number of blocks read/written/processed, same cpu times (basically) same elapsed 
times (basically).

they are identical.


Anyone who thinks different (and I know you are out there) will have to post a test case 
like the above or some scientific proof otherwise to be taken seriously....

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-573277/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1236003