Handling Materialized Views - Automated Physical Database Design and Tuning - page 141

where:
•

S i are either base table columns, column expressions, or aggregates. If a
group-by clause is present, then every S i that is not an aggregate must
be either equal to one of the grouping columns or an expression in terms
of them.

R i are range predicates. The general form of a range predicate is a
disjunction of open or closed intervals over the same column (point se-
lections are special cases of intervals). An example of a range predicate
is (1<a<10 OR 20<a<30) .

•

Z i are residual predicates, that is, the set of predicates in the query
definition that cannot be classified as either equi-join or range predicates
(e.g., a+b<10 ).
In other words, we can express the class of SPJ queries with aggregation.
The reason that predicates are split into three disjoint groups (join, range, and
residual) is pragmatic. During query optimization, it is easier to perform sub-
sumption tests for view matching if both the view and the candidate subquery
are written in this structured way. Specifically, we can then perform simpler
subsumption tests on each component and fail whenever any of the simple
tests fail. For instance, we check that the join predicates in the query are a
superset of the join predicates in the view, and the range predicates (column
by column) in the query are subsumed by the corresponding ones in the view.
The problem of determining whether two predicates are equivalent can be
arbitrarily complex. For that reason, the matching procedure usually checks
that every conjunct in the residual predicate of the view syntactically appears
in the candidate query modulo column equivalences. Otherwise, although the
view can still subsume the query, no match is produced.
We simplify the notation of a view as (

•

) , where S is the set
of columns in the select clause, T is the set of tables, J , R , and Z are the
sets of join, range, and residual predicates, respectively, and G is the set of
grouping columns.

S , T , J , R , Z , G

8.2 Search Space
Similar to the case of indexes (see Chapter 4), the search space for materialized
views can be defined by first calculating a set of materialized views for each
query in the workload and then obtaining the closure of such candidate set
under view transformations. In this section we describe these two steps in
detail.