What is the difference between count(*) and count(2) in ORACLE

Question

I'm looking at some SQL code and I'm trying to figure out what is the difference between count(*) and count(2) in oracle.

For Example

select id_number from person
where type_id = 0010
group by id_number
having count(2) > 1; 
 

Answer 1

Both expressions produce the same result.

count(<expr>) takes in account all non-null values of <expr>. 2 is a literal, non-null values, so all rows are taken into account, just like count(*) does.

You could as well express this with count(1), count('foo'), or any other (non-null) literal value.

I like count(*) better, because it is somehow clearer about what it does: every row is in the group is counted, regardless of the values it contains.

Answer 2

They mean something different:

• COUNT(*) counts returned rows.
• COUNT(<expr>) counts non-null values.

In this case you are using COUNT(2) where the expression is always not null. Therefore, it will result in the same value as counting rows.

Theoretically counting rows could be marginally faster since the data must be counted as present, but doesn't need to be really inspected. Therefore, when counting rows the engine doesn't need to materialize the column values in order to evaluate the expression, but just need to handle pointers.