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! If you want to move to the "New World" - and benefit from the awesomeness of Hadoop, without having to re-engineer your existing applications - check out Gluent, my new startup that will make history! ;-)