Difference between count(*) and count(1) – Part 1

Almost everyone have used both the functions count() and count(1). I heard different views about the difference between count() and count(1) functions. Some people says that count(1) only counts the number of rows of tables first column whereas count(*) counts rows of table by FULL TABLE SCAN. So in this post I am going to check whether is there any difference between the executions of these two functions.
I have created an EMPLOYEE table with below structure with around 31000 rows.

SQL>desc employee
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(40)
 CITY                                               VARCHAR2(40)
 PROJECT                                            VARCHAR2(40)

Difference between count(*) and count(1)

I am going to check the difference between these two function queries by considering the below three scenarios which will be three different parts of the complete post.

  1. Employee table without any index – Part 1
  2. Employee table having unique index on ID column – Part 2
  3. Employee table having with Primary Key on PROJECT column and unique index on ID column – Part 3

Scenario 1: Employee table without any index – Part 1

In this scenario, there is no index on the employee table and also there is no any constraint on the table. Now let’s check how these queries work with execution plan and auto-trace statistics. I am flushing the buffer cache before executing both the queries so that we can get accurate results.

select count(*) from employee;

employee_without_index1

select count(1) from employee;

employee_without_index2

Observations:

  • There is no any difference between the execution plans of both the queries. Both the queries are going for FULL TABLE SCAN on Employee table.
  • Both the queries have performed the same number physical reads and consistent gets.

Conclusion:

In this scenario it is confirm that there is no any difference between the executions of count() and count(1) functions. We are going to check the difference of count() and count(1) functions for two more scenarios in upcoming two posts.

Next Part : Difference between count(*) and count(1) – Part 2

Difference between count(*) and count(1) – Part 1

2 thoughts on “Difference between count(*) and count(1) – Part 1

Leave a Reply