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

plan. If only a few departments satisfy the budget constraint, and there are
appropriate indexes in the base tables but not in the materialized view, Q1 can
be e ciently answered by seeking an index on Dept(Budget, DId) to obtain
the departments with budgets over 10 M and then performing an index-based
join leveraging a clustered index on Emp(DId) . If, instead, most departments
satisfy the predicate on Budget , scanning MV1 and filtering tuples with low-
budget departments might be much more ecient because the join is avoided.
If an index on MV1(Budget, Name) is available, then the plan that seeks such
index is always better than the alternative that does not rely on MV1 .
Materialized views therefore may significantly speed up queries, especially
if they define the right indexes. At the same time, materialized views can use
significant storage (e.g., MV1 needs space that is proportional to the size of
the join) and need to be maintained for UPDATE queries. It is therefore very
natural to extend the traditional physical design problem so that it returns
the best set of materialized views and indexes, which together fit a storage
constraint and for which the given workload is expected to execute as fast
as possible. In this chapter we revisit the traditional physical design problem
discussed in previous chapters and explain how to extend the main ideas to
additionally handle materialized views.

8.1 Materialized View Definition Language
There is a trade-off between the expressive power of the view definition lan-
guage and the eciency (and even decidability) of the matching algorithms
that enable answering a query using a materialized view. In practical terms, it
is not very useful to have a very expressive view definition language if most of
the time it would not be possible to determine whether a materialized view can
be used to answer a given query. In this section we discuss a common view
definition language that is handled by most current database management
systems (DBMSs). A view is given by the following expression:

SELECT S 1

S 2 , ,... - project columns (see below)

FROM T 1

T 2

, ,...

- tables in the database

WHERE J 1 AND J 2 AND

... - equi-join predicates

R 1 AND R 2 AND ... - range predicates (see below)

Z 1 AND Z 2 AND

... - residual predicates (see below)

GROUP BY G 1

G 2 , ,... - grouping columns