count(*)

It’s quite surprising that I still see people arguing about the fastest way to “count the rows in a table”; usually with suggestions that one or other of the following queries will be faster than the rest:

• select count(*) from tab;
• select count(1) from tab;
• select count(primary_key_column) from tab;

So here’s a simple test case that I wrote several years ago to answer the question:

rem
rem     Script:         c_countstar.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2002
rem

create table t1
nologging
as
select
	rownum	id,
	owner	low_card,
	rpad('x',100,'x')	padding
from
	all_objects
;

alter table t1 add constraint t1_pk primary key(id);
create bitmap index t1_bit on t1(low_card);

analyze table t1 compute statistics;
set autotrace traceonly explain;

select count(*) from t1;
select count(1) from t1;
select count(id) from t1;

set autotrace off

Note the bitmap index on the “owner” column – it’s not referenced in any of the three queries, yet the execution plan in all three cases (9i through to 11g – 8i uses a full scan, since fast full scan on bitmap indexes wasn’t available in 8i) is:

--------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |        | 63325 |     4   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_BIT |       |            |          |
--------------------------------------------------------------------------------

The optimizer is aware that all three queries are trying to count all the rows in the table – so it chooses a plan that can do this with the smallest amount of work – and the bitmap index is the smallest object in sight that could answer the query.

If you want to look in the oraus.msg file (in $ORACLE_HOME/rdbms/mesg) then you will find the “pseudo-error”:

10122, 00000, "Disable transformation of count(col) to count(*)"

I think it’s been there since at least v7 – though I don’t have a copy I can check any more – and there’s even a note written by Mark Powell in 2001 that talks about Oracle using indexes for count(*) queries even in 7.0