Database Reference

In-Depth Information

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).

Search WWH ::

Custom Search