Characterizing the Search Space - Automated Physical Database Design and Tuning - page 70

best plan overall. If the query engine supports covering indexes, the problem
is simplified. In fact, in such situations a single optimization call is enough
because the set of candidate indexes need not include clustered indexes (a cov-
ering index has the same capabilities but is no larger than the corresponding
clustered index).
In the instrumentation approach described earlier, we can combine the in-
strumented optimization and the postoptimization step and obtain the re-
duced set of candidate indexes in a single optimization call as follows. Each
time the optimizer issues an access path request, we suspend optimization
and analyze the request. We then obtain the index that results in the most
ecient plan for such request as explained earlier, create the index (again,
see Chapter 5), and resume optimization. The optimizer would now consider
the newly created indexes and obtain the “optimal” execution subplan for
each request. Since we repeat this procedure for each access path request, the
optimizer is always given the optimal set of indexes to implement execution
plans. Therefore, the execution plan returned by the optimizer would be the
most ecient one over the space of all possible configurations.

4.2 Candidate Set for a Workload
In the previous section we discussed different ways to obtain a small superset
of the optimal configuration for a single SELECT query. When the workload
consists of multiple queries and updates, the situation is more complex. A
simple approach to obtain a candidate set for a workload W is to obtain
candidate sets for each individual query in W and join the results. As we show
next, this simple approach misses opportunities in the presence of updates or
storage constraints.

4.2.1 Suboptimal Indexes in Optimal Configurations
We now introduce two scenarios that can result in indexes, which are not
optimal for any query in the workload, to be part of the optimal configuration.

4.2.1.1 Storage Constraints

Storage constraints are very common in practice. No matter how good is a
configuration, it has to fit in the available disk space. Consider the following
queries:

Q1 = SELECT A, B, C
FROM R
WHERE 10<A<20