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

Chapter 11

Constrained Physical Database Design

In this chapter we discuss some important real-world scenarios that are not
adequately addressed by the physical design problem as defined in Part II.
We then explain how to generalize both the problem formulation and corre-
sponding techniques to address these limitations. Consider, as a motivating
example, the following query:

SELECT a, b, c, d, e
FROM R
WHEREa=10

and suppose that a single tuple from R satisfies a=10 . If the space budget
allows it, a covering index I C would be the best alternative

for the query, requiring a single input/output (I/O) to locate the qualifying
row and all the required columns. Now consider a narrow single-column in-
dex I N

= R ( a , b , c , d , e )

= ( )

. In this case, we would require two I/Os to answer the query
(one to locate the record id (RID) of the qualifying tuple from the secondary
index I N and another to fetch the relevant tuple from the primary index). In
absolute terms, I C results in a better execution plan than I N . However, the
execution plan that uses I N is only slightly less ecient than the one that uses
I C (especially compared with the simple alternative that performs a sequen-
tial scan over table R ), and at the same time it looks simpler. If updates on
columns b , c , d ,or e are possible, it might make sense to penalize wide indexes
such as I C from appearing in the final configuration. However, current tech-
niques cannot explicitly model this requirement without resorting to artificial
changes. For instance, we could simulate this behavior by introducing UPDATE
statements in the workload. This mechanism, however, is not general enough
to capture other important scenarios that we discuss below. In any case, the
previous example does not lend itself to a new “golden rule” of tuning. There
are situations for which the covering index is the superior alternative (e.g.,
there could be no updates on table R by design). In fact, an application that
repeatedly and almost exclusively executes the aforementioned query can re-
sult in a 50% improvement when using the covering index I C instead of the
narrow alternative I N .
In general, there are several scenarios for which the traditional physical
design problem statement is not adequate. In many cases we would like to

R a

199