Oracle Scratchpad

October 31, 2008

count(*)

Filed under: CBO,Oracle,Performance,Tuning — Jonathan Lewis @ 7:05 pm BST Oct 31,2008

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 (the timestamp on the file is March 2002) to answer the question:

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

5 Comments »

  1. “the fastest way to “count the rows in a table””
    A new Exadata cell ?
    Now that’ll be a challenge for your test cases, unless you can convince Oracle or HP to lend you one.

    Comment by Gary — November 1, 2008 @ 12:07 am BST Nov 1,2008 | Reply

  2. On October 11, 2004 absolute proof that count(*) is faster than count(1) has been found by Glenn A. Santa Cruz. Even Tom Kyte complies. See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789#25335122556076 .

    Comment by Jacco H. Landlust — November 2, 2008 @ 6:08 pm BST Nov 2,2008 | Reply

  3. Jacco, that was good :-)

    Comment by Yas — November 3, 2008 @ 7:32 am BST Nov 3,2008 | Reply

  4. http://www.oracledba.co.uk/tips/count_speed.htm, published Dec 2000, and I was hardly a pioneer – I published it because it had been common knowledge for some time, so I’m guessing it pre-dates that by a reasonable margin.

    Comment by Connor — November 3, 2008 @ 12:13 pm BST Nov 3,2008 | Reply

  5. […] Proof by execution plan […]

    Pingback by Why Bother | Oracle Scratchpad — September 20, 2016 @ 11:16 am BST Sep 20,2016 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.