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

  1. Subscribe to weekly updates by email or follow Social/RSS
  2. Check out my 2020 online training classes here!
    Advanced Oracle SQL Tuning training, Linux Performance & Troubleshooting training, Advanced Oracle Troubleshooting training. In addition to the live online classes, all attendees will receive personal downloadable video recordings too!