Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 173

column indexes don't necessarily lead to totally acceptable response times. Both
the join predicate column indexes and the local predicate columns often need
to be fat. Furthermore, different table access orders may lead to totally different
index requirements.
Indexing based on an assumed access path increases the probability that
the optimizer chooses the assumed access path. While another access path with
ideal indexes for that alternative could perform even better, consistent indexing
based on an assumed access path is likely to lead to shorter response times
than fuzzy indexing without any assumption about the join method and the table
access order.
Optimizer May Choose the Wrong Table Access Order
A SELECT statement referring to several tables has many more alternative access
paths than a single-table SELECT. It is not uncommon for incorrect filter factor
estimates made by the optimizer to lead to an incorrect table access order and
even to the wrong join method. Furthermore, as with single-table SELECTs, the
access path may be optimal for the average input but poor for the worst input.
With a join, the impact of a poor access path is often dramatic. Fortunately, it is
now possible to help the optimizers in many ways—with a hint, for instance. In
the early days, programmers sometimes had to replace a join cursor with several
simple cursors to achieve a good table access order, or a good join method.
Optimistic Table Design
The application we have discussed throughout this chapter looks like a data
warehouse query, but actually the problem caused by local predicates on two
large tables is quite common in operational applications; even the best access
path is far too expensive with the worst input. With this problem, the index
designer, the application programmer and the optimizer may all plead not guilty.
The problem cannot even be fixed by replacing the join with two cursors; on
the contrary, the CPU time would increase a little as the number of executed
SQL calls becomes slightly higher. The fault may lie with the database specialist
who firmly believes that redundant data in tables can never be appropriate. The
Basic Join Question should be kept in mind both when designing tables and
when writing joins. The issues associated with redundant data are discussed in
the Table Design Considerations section at the end of this chapter.
DESIGNING INDEXES FOR SUBQUERIES
From the performance point of view, subqueries are quite similar to joins. In
fact, the current optimizers often rewrite a subquery into a join before selecting
the access path. If the optimizer doesn't do this, the type of subquery may in
itself determine the table access order. Noncorrelated subqueries are normally