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

Choosing Between an Index and a Table Scan

Users are often bewildered as to why the optimizer chooses a full table scan when they believe it ought to be using an index. In a later section, this chapter explains several scenarios where the optimizer prefers a full table scan to an index. Before we delve into these scenarios, however, let's understand the essentials of how the cost-based optimizer works.

The job of the cost-based optimizer is to select the best or optimal execution plan from among a set of possible plans. The cost-based optimizer uses information such as the number of rows in a table or index, the number of distinct values for each column, and much more to estimate the cost of alternative execution plans. It then picks the execution ...