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

• CSelectBest : This configuration is the best possible one for SELECT

queries in the workload. Specifically, CSelectBest contains the best
indexes that implement each access path request generated while opti-
mizing the workload (see Section 4.1.3.3).

11.1.1 Constraint Language by Example
We next illustrate the different features of the constraint language by using
examples.

Simple constraints: To specify the storage constraint that is used in vir-
tually all physical design tuning tools we use:

≤

200M
where size(C) returns the combined size of the final configuration.
Constraints begin with the keyword ASSERT and follow the function-
comparison-constant pattern. As another example, the following con-
straint ensures that the cost of the second query in the workload under
the final configuration is not worse than twice its cost under the cur-
rently deployed configuration:

ASSERT size(C)

≤

2 * cost(W[2], COrig)
For a fixed Q , the value cost(Q, COrig) is constant, so the ASSERT
clause is valid.
Generators: Generators allow us to apply a template constraint over each
element in a given collection. For instance, the following constraint gen-
eralizes the previous one by ensuring that the cost of each query under
the final configuration is not worse than twice its cost under the cur-
rently deployed configuration:

ASSERT cost(W[2], C)

FORQINW
ASSERT cost(Q, C) 2 * cost(Q, COrig)

In turn, the following constraint ensures that every index has at most
four columns:

≤

FORIinC
ASSERT numCols(I) ≤ 4

Filters: Filters allow us to choose a subset of a generator. For instance,
if we want to enforce the previous constraint only for indexes that have
leading column col3 , we can extend the original constraint as follows:
FORIinC
WHERE I LIKE "col3,*"

// LIKE does “pattern matching”
// on index columns

ASSERT numCols(I) ≤ 4