Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Why the Optimizer May Ignore Indexes

Creating an index is never a guarantee that the optimizer will use the index when evaluating an execution plan. If a query is selecting a high percentage of rows from a table, the optimizer might determine that it can get the results faster through a full table scan instead of an index scan. Remember that when the database uses an index first, it looks up the index to obtain the ROWIDs and then uses those ROWIDs to retrieve the requested rows. If a query selects a large percentage of rows from a table, leading to a large percentage of the table's blocks being read, the database might perform a full table scan to avoid reading both the index and the table, which could end up being more expensive than just scanning ...