Database Reference

In-Depth Information

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

Search WWH ::

Custom Search