In the last two post we have seen the two scenarios to check the difference between count(*) and count(1) executions. Execution plans and statistics from both the scenarios confirmed that there is no any difference difference between the executions of these two statements. In this post I am going to check the difference by creating the primary key on the table. You will see the difference in execution plans from last two scenarios. If you have not gone through the earlier two posts, I suggest you to read those posts. Below are the links for the same.
Scenario 3: Difference between count(*) and count(1) using Primary Key
For our demonstration I am creating primary key on Project column.
We all know that when we create a primary key constraint, oracle automatically creates an index for the same. Now, Employee table has one unique index and one Primary key. In last scenario we have seen that Unique index was not used by optimizer for count() functions.
Now, I am executing the count(*) and count(1) queries against the Employee table.
select count(*) from employee;
select count(1) from employee;
- The above two execution plans shows that query is going for INDEX FAST FULL SCAN. In last two scenarios we have observed that query was going for FULL TABLE SCAN. As Primary Key does not allow NULL values to be inserted in column so query is going for INDEX FAST FULL SCAN.
- INDEX FAST FULL SCAN also performs scattered reads but it will read very less data as our primary key is based on only one column.
- The above two execution plans shows consistent gets and physical reads are very less as compared to previous two scenarios.
- There is no any difference between above two execution plans.
From all the three scenarios it is confirmed that there is no any difference between the executions of count( * ) and count(1). When table has primary key constraint enabled, optimizer chooses index corresponding to primary key or it will go with FULL TABLE SCAN.