Databases Reference
In-Depth Information
C H A P T E R 7
■ ■ ■
Tuning Index Usage
In order to tune index usage, it is important to understand the various types of index access paths
available to the cost optimizer. The chapter summarizes the most important index access paths available
to the optimizer. Often, Oracle database administrators are bewildered when the optimizer chooses not
to use what they believe is a very useful index but does a full table scan instead. This chapter devotes
attention to exploring some reasons why the optimizer might prefer a full table scan to using an index.
You'll also learn how to force the optimizer to use (or not to use) an index. Oracle provides a large
number of index-related hints to enable you to control the behavior of the optimizer regarding index
usage. You'll find descriptions of the key index-related hints in this chapter.
Before we plunge into the various ways you can direct the optimizer to access (or not to access) an
index, let's review the different access paths the optimizer can choose from.
Optimizer Access Paths
An access path is the path chosen by the optimizer to retrieve data from the database. There are two
basic types of access paths: index access paths and a full table scan. A full table scan is simply a scan of
all the rows in a table, and the optimizer uses it mostly when a query requests a large portion of a table's
blocks. Sometimes the percentage of rows retrieved by a query is relatively small, but due the way the
table's data is distributed among the blocks in the table segment, rows that satisfy the query are present
in the majority of blocks. Oracle reads blocks, and block selectivity is critical to access path selection.
Index scans, on the other hand, are typically used to retrieve a small set of a table's rows, and thus the
query needs to access fewer index (if all the data is present in the index itself) or data blocks (if the query
needs to access the table as well to retrieve all the data).
Note The optimizer is often concerned with blocks rather than rows. It is really when a query must touch the
larger portion of the
blocks
assigned to a table and below the high water mark that a table scan is often used.
