Constrained Physical Database Design - Automated Physical Database Design and Tuning - page 200

incorporate additional information into the tuning process. Unfortunately, it
is often not possible to do so by manipulating only either the input workload or
the storage constraint. For instance, we might want to tune a given workload
for maximum performance under a storage constraint, but ensuring that no
query degrades by more than 10% with respect to the original configuration.
Or we might want to enforce that the clustered index on a table T cannot be
defined over certain columns of T that would introduce hot spots (without
specifying which of the remaining columns should be chosen). Or, to decrease
contention during query processing, we might want to avoid any single column
from appearing in more than, say, three indexes (the more indexes defined on
a column, the more contention due to exclusive locks during updates).
These examples show that a single storage constraint does not model some
important scenarios in current database management system (DBMS) instal-
lations. To overcome this limitation, we need instead a generalized version of
the physical design problem statement that can handle complex constraints
in the solution space and that effectively restricts the search strategy to ad-
missible solutions. In this chapter we introduce a framework that addresses
this challenge.
11.1 Constraint Language
We next describe a simple constraint language that can express all the moti-
vating scenarios discussed earlier. The constraint language understands sim-
ple types such as numbers and strings and also domain-specific ones, such as
database tables, columns, indexes, and queries. It also supports a rich set of
functions over these data types. As an example, we can obtain the columns of
table T using cols( T ) , the expected size of index I using size( I ) , and the
expected cost of query q under configuration C using cost( q , C ) .Weuse W
to denote the input workload and the following constants to specify certain
commonly used configurations:
• C: Denotes the desired configuration, on top of which constraints are
typically specified.
• COrig: This is the configuration that is currently deployed in the
database system.
• CBase : The base configuration contains only those indexes originating
from integrity constraints. Any physical design must contain such in-
dexes, because their purpose is not improving performance but instead
guaranteeing correctness. CBase contains only mandatory indexes and
is therefore the worst possible valid configuration for SELECT queries in
the workload and the one with the lowest UPDATE overhead.