Database Reference
In-Depth Information
Chapter 13
Optimizing Data Access
An execution plan, as described in Chapter 10, is composed of several operations. The most commonly used operations
are those that access, filter, and transform data. This chapter specifically deals with data access operations, or, in other
words, how the database engine is able to access data.
There are basically only two ways to locate data in a table. The first is to scan the whole table. The second is to
do a lookup based on a redundant access structure (for example, an index) or on the structure containing the table
itself (for example, a hash cluster). In addition, in the case of partitioning, access might be restricted to a subset of
partitions. This is no different from looking up specific information in this topic. Either you read the whole book, you
read a single chapter, or you use the index or table of contents to find out where the information you're looking for is.
The first part of this chapter describes how to recognize inefficient access paths by looking at runtime statistics
provided by either SQL trace or dynamic performance views. The second part describes available access methods
and when you should take advantage of them. For each access path, the hint that you can use to reproduce it and the
execution plan operation related to it are described as well.
■
In this chapter, several SQL statements contain hints. I do that not only to show you which hint leads to which
access path, but also to show you examples of their use. In any case, neither real references nor full syntaxes are
provided. You can find these in Chapter 2 of the
SQL Reference
manual.
Note
Identifying Suboptimal Access Paths
Chapter 10 describes how to judge the efficiency of an execution plan by checking both the estimations of the query
optimizer and whether restrictions are correctly recognized. It's important to understand that even when the query
optimizer correctly chooses the optimal execution plan, it doesn't necessarily mean that this specific execution plan
will perform well. It might be that by altering the SQL statement or the access structures (for example, adding an
index), an even better execution plan could be taken into consideration. The following sections describe additional
checks that can be performed to help recognize an inefficient access path, what might be causing it, and what you can
do to avoid the problem.
Identification
The most efficient access path is able to process the data by consuming the least amount of resources. Therefore, to
recognize whether an access path is efficient, you have to recognize whether the amount of resources used for its
processing is acceptable. To do so, it's necessary to define both how to measure the utilization of resources and what
