我觉得count(*)中的*与count(1)中的1就象select * from...中的*与select 1 from ...中的1一样.
什么时候用count(*) 什么时候用count(1) ? 那个效率更高？
---from asktom.comnothing, 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:[email protected]> alter session set sql_trace=true;Session [email protected]> select count(*) from all_objects; COUNT(*) ---------- [email protected]> select count(1) from all_objects 2 / COUNT(1) ---------- 27044and 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 1select 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 1Same 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....
而count(1)要针对结果集进行处理 ，Oracle是没有办法优化的，此时是按照字段来处理的
我认为还是count(*)要快一点
count(1)
count( [distinct|all] expr) ....
------------------------------------
我认为：
* 代表全部字段
1 代表查询的第一个字段
distinct 是去掉重复的字段
另 count 的全部语法你可以参考下面网页：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions26a.htm#SQLRF00624
需要你在oracle有注册才能，注册是免费的。
那个效率更高？
take the same amount of resources.You can see this via:[email protected]> alter session set sql_trace=true;Session [email protected]> select count(*) from all_objects; COUNT(*)
----------
[email protected]> select count(1) from all_objects
2 / COUNT(1)
----------
27044and 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 1select 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 1Same 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....
我得到答案了！
oracle 7.x及其以前的版本，count(*）要快于count(1)
oracle 8以后，他们是一样的！
说一样的都有分！呵呵！