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
“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 GMT Nov 1,2008 |
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 GMT Nov 2,2008 |
Jacco, that was good :-)
Comment by Yas — November 3, 2008 @ 7:32 am GMT Nov 3,2008 |
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 GMT Nov 3,2008 |
[…] Proof by execution plan […]
Pingback by Why Bother | Oracle Scratchpad — September 20, 2016 @ 11:16 am BST Sep 20,2016 |
[…] I’ve pointed out in the past, in fact more than once, that count(*), count(1), count(declared-non-null-column) will all do the […]
Pingback by count(*) – again | Oracle Scratchpad — February 25, 2020 @ 1:24 pm GMT Feb 25,2020 |
[…] gets to the Predicate Information. People still ask which is faster, count(1) or count(*) – the argument should have died back in Oracle 7 […]
Pingback by push_having_to_gby() | Oracle Scratchpad — September 11, 2020 @ 11:29 am BST Sep 11,2020 |