Oracle: Count(*) or Count(1) that is the question.

1 October 2015, Monika Mitura

Count(*) or Count(1) that is the question.

For many years on Oracle’s forums there has been ideological fight: COUNT(*) or COUNT(1). Each option has its true believers and opponents.

The most common argument of 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, isn’t it?  Unfortunately it is simply not true.

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’)?

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 discussion 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 rewrite COUNT(1)  to COUNT(*).

 

Links to heated debates:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40208915257337

https://community.oracle.com/thread/613439

http://www.databaseskill.com/4564328/

http://dba.stackexchange.com/questions/2511/what-is-the-difference-between-select-count-and-select-countany-non-null-col

http://www.dbforums.com/showthread.php?1605397-difference-in-count(*)-and-count(1)

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

Tagged with: , , , , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!