Select COUNT(*) and COUNT(column) are different things!

Tanel Poder

2009/08/22

Every now and then I see someone wondering why Oracle is “returning wrong results” for some count queries when counting using COUNT(column_name) instead of COUNT(*) or COUNT().

Oracle is actually returning correct results, its just that sometimes the people asking the questions haven’t realized that COUNT(column) is something semantically different from COUNT(*).

COUNT(*) operation counts all rows fed to it by execution plan branch under it.

COUNT(column) operation on the other hand counts all non-null values in that column from rows fed to it by execution plan branch under it.

And here’s a little example:

SQL> select count(*) from v$session;

 COUNT(*)
----------
        23

SQL> select count(username) from v$session;

COUNT(USERNAME)
---------------
              1  <<-- only one non-null value in that column

SQL> select count(nvl(username,'blah')) from v$session; <<-- lets replace NULLs with non-nulls

COUNT(NVL(USERNAME,'BLAH'))
---------------------------
                         23
[Comments]
Click on Tweet to comment or ask a question! Keep the "via @tanelpoder" in the tweet text to notify me.
NB! Check out my 2019 online training classes here! Practical Linux Performance & Application Troubleshooting training (new), Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training. In addition to the online classes, all attendees will receive personal downloadable video recordings too!