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

all view requests generated during optimization. Each view request encodes
the most specific materialized view that can be used to answer the generating
logical subexpression.

Indexes on materialized views: A materialized view is viewed by the
optimizer as a regular table that can be used to evaluate queries. We
can then define indexes on materialized views. We can combine the
approaches in the previous sections with those in Chapter 4 to obtain
a candidate set of materialized views and indexes (defined over either
base tables or materialized views themselves). Once we obtain the initial
candidate set of indexes and views for a workload using any of the
previously described approaches, we extend it via transformations. We
next describe the merge and reduction transformations that characterize
the search space for the physical design problem, including both indexes
and materialized views.

8.2.2 View Merging
Merging two materialized views V 1 and V 2 results in a new materialized view
V M that reduces the amount of redundancy between V 1 and V 2 . The resulting
view V M is usually smaller than the combined sizes of V 1 and V 2 at the expense
of longer execution times for queries that exploit V M instead of the original
ones. Consider the following two materialized views:
V 1 = SELECT a,b

V 2 = SELECT b,c

FROM R FROM R

WHERE a < 10 WHERE b < 10

Suppose that the space required to materialize both V 1 and V 2 is too large. In
this case, we can replace both V 1 and V 2 by the alternative V M , defined as
V M = SELECT a,b,c
FROM R
WHERE a < 10 ORb<10

The main property of this alternative view is that every query that can be
answered using either V 1 or V 2 can also be answered by V M . The reason is that
we can rewrite both V 1 and V 2 in terms of V M by adding appropriate filter
predicates. If a significant fraction of the tuples in R satisfy both R

. a < 10 and

10, the size of V M might be much smaller than the sum of the sizes of
the original views V 1 and V 2 . In fact, V M is the smallest view that can be used
to generate both V 1 and V 2 . It is also important to note that queries that are
answered using V 1 or V 2 are less eciently answered by V M . The reason is that
V M is a generalization of both V 1 and V 2 and contains additional, nonrelevant
tuples with respect to the original views. In other words, by merging V 1 and
V 2 into V M we are effectively trading space for eciency. Given V 1 and V 2 ,

R . b <