How to Supercharge SELECT COUNT(*) in Oracle: 6 Proven Optimization Tricks

Optimization Steps

The simple query select count(*) from t can be dramatically accelerated using a series of Oracle-specific techniques.

Ordinary approach No special technique applied; logical reads = 1048.

Add a B‑tree index Create a standard B‑tree index on the relevant column(s). Logical reads drop to 372.

Use a bitmap index Create a bitmap index, which reduces logical reads from 372 to just 6.

Bitmap index example: table T has columns ID, NAME, SEX, STATUS; SEX contains only "male" or "female" (or NULL).

Materialized view Apply a materialized view to cache pre‑aggregated results. Logical reads fall from 6 to 3. This technique is best when the underlying data changes infrequently, trading space for speed.

Result‑set cache Oracle 11g provides a result‑set cache stored in shared memory. When the same query is executed and its result set is cached, almost all overhead is avoided, reducing logical reads to 0.

Business‑driven rewrite Rewrite the query to select count(*) from t where rownum=1 . This forces Oracle to stop after reading the first row, making the operation constant‑time regardless of table size.

The rewritten query is logically equivalent to the original count but accesses only the first row, eliminating any performance concerns.

Optimization Summary

The repeated improvements illustrate three core principles:

Deep understanding of the SQL execution plan and index structures.

Selecting the appropriate technique (B‑tree, bitmap, materialized view, cache, or query rewrite) based on the specific workload.

Aligning optimization with business requirements to achieve equivalent, faster results.

These classic tactics turn a seemingly trivial SELECT COUNT(*) into a high‑performance operation.

Key Takeaway

Effective SQL tuning combines index knowledge, advanced Oracle features, and business‑driven query transformations.