拍拍尘土

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