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(<constant>).
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
NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!