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

Chapter 8
Handling Materialized Views
Similar to indexes, materialized views are redundant data structures that can
be used to speed up query processing. A view is a virtual table whose content
is derived from base tables by using a subset of Standard Query Language
( SQL ) (we discuss the language used to define views in Section 8.1). A view is
materialized by defining an appropriate clustered index. Then, the content of
the view is persisted and maintained as if it were a regular table, which can be
indexed and leveraged to answer queries eciently. Consider again the query
from Chapter 2 that returns the names of employees working in departments
with budgets larger than $10 million:
Q1 = SELECT Emp.name
FROM Emp, Dept
WHERE Emp.DId = Dept.DId
AND Dept.Budget>10M
Suppose that we create a materialized view MV1 that precomputes the join be-
tween employees and departments. This view, which we denote MV1 , is defined
as follows:
MV1 = SELECT Emp.EId, Dept.DId, Emp.name, Dept.Budget
FROM Emp, Dept
WHERE Emp.DId = Dept.DId
Using MV1 , we can rewrite the original Q1 into an equivalent form as follows:
Q2 = SELECT MV1.name
FROM MV1
WHERE MV1.Budget>10M
In general, a view must subsume a query subexpression to be useful in an-
swering the full query (i.e., the view definition must contain at least all the
tuples required in the query subexpression as well as means to filter out the
tuples that are not relevant). MV1 subsumes the whole Q1 , since the results
of MV1 are a superset of those of Q1 .For Q1 and Q2 to be equivalent, we
add the filter condition MV1.Budget > 10M in Q2 , generally referred to as a
compensating action . Just by syntactically looking at Q1 and Q2 , it cannot
be determined which alternative would lead to the most ecient execution
139