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:
Output after applying count(*) on Stu table;
Output after applying count(1) on Stu table:
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:
