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

by the input set of views (any generalization of a reduced view can also be
used, but this is covered by the merge operator).

8.3 Cost Model
Extending the cost model so that it also handles materialized views re-
quires modifying the optimizer to support what-if optimization on views. The
changes require some nontrivial engineering effort but conceptually are sim-
ilar to the extensions required for what-if optimizations using hypothetical
indexes over base tables. One additional challenge is estimating the size of an
index over a materialized view. Unlike indexes on base tables, we do not know
the size of the materialized view unless we actually create it by executing
its defining query. However, we can approximate the number of tuples in the
materialized view by using the cardinality estimation module of the optimizer
itself to estimate the number of tuples returned by the view definition. More
accurate techniques can be used in special cases, such as using sampling for
single-table views or those that use foreign-key joins.

8.3.1 Cost Upper Bounds
Some enumeration strategies rely on the capability of approximating the cost
of a query under a hypothetical configuration without issuing optimization
calls (e.g., see Section 6.2). This functionality was discussed in Chapter 5 in
the context of indexes defined on base tables. Consider the execution plan
p for query q obtained under configuration C , and suppose that we want to
approximate an upper bound on the cost of q under C . Analogous to the case
of base table indexes, we identify all subplans of p that use index strategies
over views and then obtain an upper bound on the cost of implementing such
subplans using indexes in C .
Consider a subplan that uses an index over view V . Further assume that
C contains some view V that is obtained from V using merge or reduction
operations. In that case, we know that the optimizer would match V whenever
it matched V and therefore can estimate the cost of answering the subquery
using V and corresponding compensating actions. While this idea is clear,
a number of subtleties need to be taken into account. We illustrate some of
these using views V 1 , V 2 , and V M =

V 1 ⊕ V 2 :

V 2 =SELECT a, SUM(c) V M =SELECT a, b, c

V 1 =SELECT a, b

FROM R FROM R FROM R

WHERE a<10 AND b<20 WHERE a<20 WHERE a<20

GROUP BY a

Let us assume that C includes both V 1 and V 2 , and C includes V M instead.
Suppose that query q under C seeks an index I 1 = V 1 ( b , a ) for some range