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).