In the last post I have demonstrate the scenario for employee table without any index to differentiate the executions of count() and count(1). That demonstration proved that there is no any difference between count() and count(1) executions. If you have not gone through that post then I am recommending to read that post before this post. Below is the link for the same.
In this post I am going to take demonstration of second scenario in which I am going to create UNIQUE index on ID column of EMPLOYEE table.
Scenario 2: Employee table having unique index on ID column – Part 2
In this scenario I am creating an UNIQUE index on UD column of Employee table and there in no any constraint on the employee table.
Now, I am executing the count(*) and count(1) queries against the Employee table.
select count(*) from employee;
select count(1) from employee;
- 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.
Why oracle has not used an index?
Oracle does not stored the NULL values in the index and UNIQUE constraint allows to insert NULL values in the column as no two NULL values can be compared. If oracle uses created UNIQUE index for this count, result will be wrong in case of NULL values in the column.
This demonstration shows us that there is no any difference between COUNT(*) and count(1) function even if there is an UNIQUE index on the table. In the next part, I am going to check the difference of these function’s execution by creating primary key on the table.