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

such soft constraints by adding a SOFT keyword in the ASSERT clause.
The resulting constraint thus becomes

FORQinW
WHERE type(Q) = SELECT
SOFT ASSERT cost(Q, C) ≤ cost(Q, COrig) / 1.1
Note that the traditional optimization function (i.e., minimizing the cost
of the input workload) can be then specified as follows:

FOR Q in W
SOFT ASSERT sum(cost(Q, C)) = 0
If no soft constraints are present in a problem specification, we implicitly
add the previous soft constraint and therefore optimize for the expected
cost of the input workload. In general, however, soft constraints allow
significantly more flexibility while specifying a physical design problem.
For instance, suppose that we are interested in the smallest configura-
tion for which the cost of the workload is at most 20% worse than that
for the currently deployed configuration (this problem statement is use-
ful to eliminate redundant indexes without significantly degrading the
expected cost of the workload). We can specify this scenario using soft
constraints as follows:
FORQinW
ASSERT sum(cost(Q, C)) ≤ 1.2 * sum(cost(Q, COrig))
SOFT ASSERT size(C) = 0

11.1.2 Motivating Examples Revisited
We next show that although this simple language is able to specify all the
motivating examples in the previous section, the following constraint ensures
that no column appears in more than three indexes to decrease the chance of
contention:

FOR T in TABLES
FOR col in cols(T)
FOR I in C WHERE I LIKE "*,col,*"
ASSERT count(I) ≤ 3
The next constraint enforces that the clustered index on T must start with
a , b ,or c :

FOR I in C
WHERE clustered(I)
ASSERT I LIKE "(a,*)|(b,*)|(c,*)"
Note that the ASSERT clause is a predicate and does not follow the pattern
function-comparison-constant introduced earlier. We thus implicitly replace a
predicate

ρ with δ(ρ) =1, where δ is the characteristic function (i.e., δ( true ) =1

and δ( false ) =0).