When Should I Use an Oracle Index?

By Richard Niemiec on April 20, 2013

In Oracle version 5, many DBAs called the indexing rule the 80/20 Rule; you needed to use an index if less than 20 percent of the rows were being returned by a query. In version 7, this number was reduced to about 7 percent on average, and in versions 8i and 9i, the number was closer to 4 percent. In versions 10g and 11g, Oracle is better at retrieving the entire table, so the value continues to be in the 5 percent or less range, although it depends not only on the number of rows but also on how the blocks are distributed as well. Table 1 shows when an index should generally be used (in V5 and V6 for rule-based optimization and in V7, V8i, V 9i, V10g, and V11g for cost-based optimization).
However, based on the distribution of data, parallel queries or partitioning can be used and other factors need to be considered. If the table has fewer than 1000 records (small tables), then the graph is also different. For small tables, Oracle’s cost-based optimizer generally uses the index when only less than 1 percent of the table is queried.
Figure 1.   When to generally use an index based on the percentage of rows returned by a query


This graph shows you the progress in versions of Oracle. The lower the percentage of rows returned, the more likely you would use an index. This graph shows the speed of a full table scan becoming faster. Because of the many variables starting with Oracle 9i, the percentage could continue to decrease as the trend shows happening from V5 to V8i, or it could increase slightly, depending on how you architect the database. In Oracle 9i, Oracle 10g, and Oracle11g, you create where the graph goes and Exadata and Exalogic enhancements can further alter this graph (where percentage could decrease to less than 1 percent); your choice may depend on how the data and indexes are architected, how the data is distributed within the blocks, and how it is accessed.

When a small number of rows (“small” is version and hardware dependent) are returned to meet a condition in a query, you generally want to use an index on that condition (column), given that the small number of rows also returns a small number of individual blocks (usually the case).

Related Posts


  1. With the more advent of technology, through various releases of Oracle, it has been using lesser and lesser records while considering an index. So definitely if it’s 11g then surely 5 percent or lesser rows of the entire table data is contender for an Index to be used. We need to always keep in mind that during Index traversal as we have to depend on sequential search and random search in compare to multi block read of Oracle full table scan so in case of bigger data than 5 percent of table rows it’s rare that Oracle will use index scan. Otherwise it can be very costly. Using all the indexed columns in your SELECT for a smaller percentage of data as mentioned above will result in Index Fast Full Scan, which is very fast.

Leave a Reply