Misconception:
Count (1) is better than count (*)
Jeremiah Wilton
The idea that you get faster results by selecting count(1) or count(’x’) or some similar literal value rather than count(*) has been poplar ever since I began working with Oracle in 1994.
The truth is that Oracle8's cost-based optimizer will do an index fast full scan on the first unique index on a not-null column (usually the primary key) if it is available. If no such index is available, a full table scan will be performed. This behavior is the same under the cost-based and rule-based optimizers no matter what you put in the parentheses of the count function.
Under the rule-based optimizer, count() always results in a full table scan.
Here's a proof that demonstrates that count(*) is the same as count(<any literal>), using logical reads and explain plan, instead of wall clock time. It shows that the various count() theories are, in fact, just legend. Your session performs the same amount of work no matter which method you use.
Take an imaginary table, foo, with 955190 rows, and a primary key. Rows will be counted by sid 90. In a separate session, I record the starting statistics of the session (read from another session, so stats are not affected by the statistics queries):
SQL>
select sn.name, ss.value from v$session s, v$sesstat ss, v$statname sn where
s.sid = ss.sid and ss.statistic# = sn.statistic# and sn. name = 'session
logical reads' and s.sid = 90;
NAME VALUE
------------------------------
----------
session
logical reads 130054
First, count the usual way:
SQL>
select count (*) from foo;
COUNT(*)
--------
955190
select
statement [cost = 303, cardinality =
1.00, bytes = 0]
2 2.1
sort aggregate
3
3.1 index fast full scan pk_f_foo_id unique [cost = 303, cardinality =
952723.00, bytes = 0]
Querying v$sesstat, I see that the session logical reads have increased to 132006 (an increase of 1952 logical reads).
Then, I count rowids:
SQL>
select count (rowid) from foo;
COUNT(ROWID)
------------
955190
select
statement [cost = 303, cardinality =
1.00, bytes = 7]
2 2.1
sort aggregate
3
3.1 index fast full scan pk_f_foo_id unique [cost = 303, cardinality =
952723.00, bytes = 6669061]
The session logical reads increase to 133958 (an increase of 1952 logical reads).
Finally, I count the PK column:
SQL>
select count (foo_id) from foo;
COUNT(FOO_ID)
-------------
955190
select
statement [cost = 303, cardinality =
1.00, bytes = 0]
2 2.1
sort aggregate
3
3.1 index fast full scan pk_f_foo_id unique [cost = 303, cardinality =
952723.00, bytes = 0]
The session logical reads increase to 135910 (an increase of 1952 logical reads).
So in each case, the number of logical reads and the explain plans are identical. This proof shows that the three methods of counting are identical.