新发表

新评论

留言信息

链接收藏

免费注册博客通道

快速登陆

coun(1) and count(*) differenct

在CSDN上问人，越问越没兴趣呆下去，不过感谢一朋友回复一地址：

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1156151916789

这个文章全面一点
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1156159920245

费劲看完，大体明白是这样的回事情！

—————————————————————————————

You Asked (Jump to Tom's latest followup)

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....

第一篇文章有说道原理，注意它上面的连接地址，看的我头晕。但是看了半天，还是把它看完了，第二个有测试的结果，结果已经列在上面！

Posted by jser | 阅读全文() | 回复(0) | 引用通告() | 编辑