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

8.2.1 Candidate Selection
The mechanisms used for candidate selection of indexes can naturally be ex-
tended to deal with views. However, the specific details are more complex due
to the additional expressive power of views. The obvious approach of choosing
a materialized view that exactly matches each query is not generally feasible.
The reason is that the materialized view language is more restrictive than
the query language. Instead, the idea is to obtain, for each query, the set of
materialized views that covers the input query as much as possible.

8.2.1.1 Parsing-Based Approach

This approach requires understanding the structure of the query (or, as we
discussed in Section 4.1.2 for the case of indexes, the corresponding execution
plan). Once we gather information about the structure of the query, we can
identify the maximum subexpressions that can be expressed in terms of the
view definition language. Note that a single query might define several such
subexpressions, connected by operators that are outside the view definition
language (e.g., correlated subqueries, or UNION operators). Each subexpression
can then be translated into a candidate materialized view, and the resulting
views become the candidate set of each query in the workload.

8.2.1.2 Instrumentation Approach

The aforementioned approach does not require changes to the query optimizer
but relies on a complex logic to determine subexpressions that are expressible
in the view definition language. Moreover, since queries can be expressed in
multiple equivalent ways, sometimes we can miss opportunities by focusing on
a specific parsing tree. As a simple example, sometimes nested subqueries can
be folded into the main query block. Unless the previous approach can leverage
such transformations, it will miss the larger candidate view and obtain only
the smaller candidate views that correspond to the inner and outer query
blocks. At the same time, including all this specialized knowledge into the
candidate selection module would duplicate much of the work done by the
optimizer itself.
This discussion motivates an extension to the instrumentation approach
described in Section 4.1.3 for the case of indexes. Specifically, we note that
query optimizers have a specific view-matching component. This component
takes as input a query subplan p and tries to match p with existing views
(possibly using compensating actions) to explore plans that leverage materi-
alized views. In doing so, the optimizer needs to analyze the input subquery
and essentially to rewrite it in a way that can be better manipulated for doing
view matching. We can then instrument the optimizer such that every time
the view-matching-component is called with a subexpression p we store the
view request at the root of the originating logical plan. At the end of optimiza-
tion, we return, along with the execution plan and index requests, the set of