Practical Aspects in Physical Database Design - Automated Physical Database Design and Tuning - page 121

syntactic approach but requires optimizing each query in the workload at
least once, which can be expensive.
7.3 Tuning Modes
The traditional physical design problem asks for the best configuration that
fits in a storage constraint and is expected to improve the execution time of
the input workload as much as possible. Certain scenarios, however, require
slight variations of this problem statement [see Figure 7.1(3)]. In this section
we discuss some common alternatives.
Keep existing structures. If the current physical design is reasonably
tuned for performance and there are a few queries that require additional
tuning, a common alternative is to require that the final configuration
include the current one. That is, we can add new indexes to the current
configuration but not remove existing indexes. Note that the current
configuration can also include hypothetical indexes, with the implicit
meaning that the final configuration should include them as well. The
techniques discussed in previous chapters can be easily adapted to this
scenario, by adding the desired set of indexes to each candidate config-
uration and disallowing invalid configurations (e.g., those for which two
clustered indexes are defined over the same table).
Remove unused indexes. This approach is useful when we want to re-
duce the size of the current configuration without significantly compro-
mising the quality of execution plans. In this scenario, the enumeration
space is composed of all and only indexes in the current configuration,
and we look for the subset of indexes that result in the smallest ex-
ecution time. Usually, indexes are removed because of updates in the
workload or simply because they are not useful for any query in the
workload (note that, naturally, given two configurations with the same
expected cost for the workload, we should pick the smallest one). A
variation of this technique is to consider only indexes that are either
in the original configuration or can be obtained by merging, splitting,
or reduction operations from them. In a sense, the idea is to refine the
original configuration so that it is not much worse than the original one
but is significantly smaller in size.
Index restrictions. Sometimes it is useful to restrict the search space to
a certain subset of candidate indexes. A common alternative, when the
clustered indexes of a database are predefined by design choices, is to
consider tuning only nonclustered indexes in the configuration. Other