Contents

For many years, on Oracle’s forums, there has been an ideological fight: COUNT(*) or COUNT(1)? Each option has its true believers and opponents. Let’s dive into this topic!

Update: The article was updated on 30th October 2015. I added a part regarding the proof that Oracle internally rewrites Count(1) into Count(*).

Is Count(1) faster than Count(*)?

The most common argument used by the group which supports the use of COUNT(1) is the assertion that COUNT(1) is faster than COUNT(*). According to this theory, COUNT(*) takes all columns to count rows and COUNT(1) counts using the first column: Primary Key. Thanks to that, COUNT(1) is able to use index to count rows and it’s much faster. It sounds reasonable, doesn’t it?

Unfortunately, it is simply not true. And it’s time to dispel this widely spread myth.

The general definition of the COUNT function looks like the following:

COUNT (<expression>)

COUNT function counts all of not empty (non-null) occurrences of expression <expression>.

COUNT(1) is nothing other than COUNT(<expression>) where the Expression = 1 => and „1” is obviously not null.

COUNT(*)

There is not too much to explain – it just counts all records in table.

 

COUNT(*) COUNT(MANAGER_ID) COUNT(1) COUNT(‘BANANA’)
107 106 107 107

 

As you can see count(1), count(‘banana’) and count(*) do the same thing. So maybe we should use COUNT(‘banana’)?

So, what to choose? Count(*) or Count(1)?

If you want to have clear and comprehensible code, COUNT(*) is more appropriate.  COUNT(*) at first glance indicates what will be done. Asterix is a typical sign of a meaning „everything” so you can assume that the function counts ALL rows.

As shown by stormy discussions on forums, COUNT(1) is not so obvious. If function COUNTS only the first column or the non-null values or uses index? Opinions are divided and each version has its die-hard followers.

To sum up – if you want to count all the rows in a table, you should use COUNT(*). If you want to count not null occurrences in a table – the best solution is COUNT(<expression>).

Finally, for all the sceptics, the last argument – all of you, no matter what you do, you end up doing COUNT(*) – Oracle internally rewrites COUNT(1) to COUNT(*).

Does Oracle really rewrite Count(1) to Count(*)?

After the initial publication of this article, I was asked about the proof that Oracle internally rewrites Count(1) into Count(*). Follow the steps below and check for yourself. 🙂

1. Create CNT_TEST table

2. Start TRACE 10053

3. Run query

4. Stop TRACE

5. Search sql id

6. Save TRACE file

7. In the TRACE file, search for the string ‘Final query after transformations:’
The result:

 

Test results:

Count(*) select count(*) cnt_str from all_objects; Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_STR” FROM “SYS”.”CNT_TEST” “CNT_TEST”
Count(1) select count(1) cnt_1 from all_objects; Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_1” FROM “SYS”.”CNT_TEST” “CNT_TEST”
Count(id) – count on Primary Key select count(id) cnt_id from all_objects; Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_ID” FROM “SYS”.”CNT_TEST” “CNT_TEST”
count(object_id) –count on not NULL column select count(object_id) cnt_not_null_coll from all_objects; Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(*) “CNT_NOT_NULL_COLL” FROM SYS.”OBJ$” “SYS_ALIAS_16″,SYS.”USER$”
Count(OBJECT_TYPE) –count on nullable column select count(OBJECT_TYPE) cnt_nullable from all_objects; Final query after transformations:******* UNPARSED QUERY IS *******SELECT COUNT(DECODE(“SYS_ALIAS_17″.”TYPE#”,0,’NEXT OBJECT’,1,’INDEX’,2,

 

Last but not least, should you want to read more, here are links to some of the heated Count(*) vs Count(1) debates:

This post was published originally at my blog, which you can find here how2ora.blogspot.com.

Share