Oracle: Difference Between Count(*) and Count(1)

Profile picture for user akshita.goel
Submitted by akshita.goel on

Count function in Oracle is used to count the number of elements present in the Table. Let us understand the concept of count(*) and count(1) with the help of an example. Consider the Stu table as given below:


Count(*) vs Count(1)

Output after applying count(*) on Stu table;

Count(*) vs Count(1)

Output after applying count(1) on Stu table:

Count(*) vs Count(1)

We noticed that the output of both the commands is the same. we both got 4 as an output because we have in total 4 rows.

Then what makes these two different from each other?

Count(*) function takes more time to run when we deal with thousands of data. And, Count(1) takes less time as compared to count(*).

Reason:

The count(*) function in Oracle iterates all the column present in the table and then provide us with the output. However, count(1) function interates only a single column and provide with the output.

NOTE: 1 in Count(1) function is just a harcoded values. It does not specifies any rows or columns. However, we can use any other numerical or string value in place of 1 and output will be the same. Follow the picture below to understand it better:

Count(*) vs Count(1)