The final answer to the age old debate of count(*) vs count(1).Absolute proof
that count(*) is faster than count(1).
sql->alter session set events '10046 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:00.10
sql->select count(1) from dual;
COUNT(1)
----------
1
1 row selected.
Elapsed: 00:00:00.40
sql->select count(*) from dual;
COUNT(*)
----------
1
1 row selected.
Elapsed: 00:00:00.40
-- TKPROF:
select count(1)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=87 us)
1 TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=71 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.11 3.11
********************************************************************************
select count(*)
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=94 us)
1 TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=79 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 2.30 2.30
--------------------------------------------------------------------------------
count(1) shows "SQL*Net message from client" = 3.11
count(*) shows "SQL*Net message from client" = 2.30
Research into the time problem led first to a comparison of the statements.
Careful examination revealed a subtle difference: a one byte discrepancy at
offset 14 of the SQL statement ( "*" vs. "1" ). Realizing this difference,
I was able to form two separate cases and document execution steps for each.
case 1: count(*)
----------------
1) left pinky holds down left shift key
2) right middle finger hits "("
3) right middle finger hits "*"
4) right ring finger hits ")"
Note that steps 1 and 2 are very nearly simultaneous, as are steps 3 and 4,
due to parallelism between the two hands. The majority of the time spent
in this case is the movement of right middle finger between steps 2 and 3
case 2: count(1)
----------------
1) left pinky holds down left shift key
2) right middle finger hits "("
3) left pinky releases left shift
4) left ring finger hits "1"
5) left pinky holds down left shift key
6) right ring finger hits ")"
Note that steps 1 and 2 are very nearly simultaneous, as are steps 5 and 6.
The majority of the time spent in this case is steps 3, 4, and 5.
Analysis: case 1 has 33% FEWER steps than case 2. Even with pipelining involved
(using both hands at once), case 2 must still perform more work to accomplish
what Oracle will ultimately consider the same thing. This may be defeated,
however, by clever use of the copy/paste subsystem of the operating system.
Using high-speed overhead keyboard cameras may reveal further important
information.
To ensure this was a reproducible case, I performed it on multiple machines
with similar findings. I furthermore discovered that both cases suffered
reduced performance after a 24 hr lack of sleep.
The point is, my argument is equally as valid as some of the arguments I've
heard: utterly ridiculous and inconclusive. Unless one is an Oracle engineer
responsible for the code that actually interprets and
optimizes count(*), then you really can't argue that count(1) is faster than
count(*). You must provide irrefutable conclusive evidence to prove your case
that one is faster than the other, and that case must rest the test of this exercise
in futility.
