Oracle COUNT(*) Optimization — Using Index Fast Full Scan

 

When developing applications, there are times when you need to quickly check whether certain data exists in a table. 

The most common method is using COUNT(), but this can be optimized for much better performance. 

Today, we’ll explore how to speed up COUNT() in Oracle using Index Fast Full Scan.

1. COUNT(*) Execution Methods

When performing COUNT(*), Oracle can scan data in two ways:

• Full Table Scan

• Full Index Scan

The faster method depends on the table structure and data distribution. Although scanning the index is often faster, Single Block Scan can sometimes limit the performance. What if we use Multi Block Scan for the index area? We can achieve significantly better speed.

2. What is Index Fast Full Scan?

Index Fast Full Scan reads the index area in parallel by dividing it into multiple blocks. Using this method, you can quickly perform COUNT(*) without directly reading the table’s data. For this, Oracle provides the INDEX_FFS hint.

3. Execution Plan Comparison

Basic COUNT(*) Execution

SELECT COUNT(*) FROM EMP;
Execution Plan
------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE
1        0  SORT  (AGGREGATE)
2        1    TABLE ACCESS (FULL) OF ‘EMP’

Using Full Index Scan

SELECT /*+ INDEX(EMP pk_emp) */ COUNT(*) FROM EMP;
Execution Plan
------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE
1        0  SORT  (AGGREGATE)
2        1    INDEX FULL SCAN OF ‘pk_emp’ (UNIQUE) (cost=30)

Using Index Fast Full Scan

SELECT /*+ INDEX_FFS(EMP pk_emp) */ COUNT(*) FROM EMP;
Execution Plan
------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=CHOOSE
1        0  SORT  (AGGREGATE)
2        1    INDEX (FAST FULL SCAN) OF ‘pk_emp’ (UNIQUE) (cost=2)

4. Performance Test Results

Testing with 50,000 records produced the following results:

SELECT COUNT(idx) FROM TESTBBS;
-- Execution time: 225ms

SELECT /*+ INDEX_FFS(TESTBBS TESTBBS_IDX) */ COUNT(idx) FROM TESTBBS;
-- Execution time: 17ms

We can see that using Index Fast Full Scan improves performance by over 10 times.

5. Conclusion

If you want to speed up COUNT() in Oracle, make active use of Index Fast Full Scan. 

This method is particularly effective when working with large datasets and frequent COUNT() queries. 

The hint is simple to use, so give it a try and see the performance boost for yourself!