Select count(*) from statement is one of the most common SQL statements that we will find on databases. This simple SQL may have great influence on the performance of the database if it does not execute in an efficient manner.
So, to make select count(*) statement run faster, we need to ensure the Oracle optimizer to count the table by reading the index instead of the table. In other words, we want CBO to do the index fast full scan(FFS) rather full table scan (FTS)when counting the rows.
Note that not all the indexes can be used by CBO, for the most simplest select count(*) statement -- the one without where clause and join, the CBO will choose to utilize a fast full scan(FFS) on index when the table either one of the following conditions is fulfilled:
1) the table has primary key constraint, or
2) the table has indexes on unique key columns, or
3) the table has indexes not null columns, or
4) the has bitmap indexes (only available in Oracle Ent Edition).Update on 8-Sep-2010
Jonathan talked about how not null constraint and how it affect the the performance on query. It is surprised to hear this little feature would embarrassed in front of the client. Anyway, he had an update on his blog(http://jonathanlewis.wordpress.com/2010/09/05/not-null/) about this not null issue in with examples. It is worth to read!
Filed under:
PL/SQL