Databases Reference
In-Depth Information
query includes a predicate on region where REGION = 'NEW YORK', it is likely that
'NEW YORK' is merely incidental, and that other locations are going to be submitted
for this predicate. So grouping by region is a reasonable strategy.
The common technique used for generating candidate materialized views is to
exploit the query graph that the query compiler generates for each query during compila-
tion. The query compiler's optimizer runs its query optimization rules in reverse to gen-
erate a small set of candidate access structures that would ideally benefit a given query. In
addition to that technique, an additional technique called “multiquery optimization” (or
mass query optimization) was also published that does an excellent job of looking for
materialized view candidates based on a simultaneous analysis of groups of queries.
The Oracle SQL Access Advisor also generally includes a COUNT(x) column
matching every SUM(x) column, which they have found to be a useful strategy. For
example, using Oracle SQL Access Advisor, if a query contains SUM(x), a recommen-
dation for a materialized view containing SUM(x) will also contain COUNT(x). This
idealization enables the computation of AVG(x) from SUM(x)/COUNT(x), which is a
common occurrence.
For all three products it is possible to see a recommended materialized view that is an
exact copy of the request query. This usually occurs with the most costly and complex
queries where a materialized view devoted to just that one complex query is justified.
In principle a materialized view can be created for every distinct query on the sys-
tem, but the storage and maintenance requirements would be completely unreasonable
for most systems. Finding a good selection of materialized views that has the most ben-
efit often requires finding complex materialized views that provide full or partial benefit
to multiple queries, rather than selecting the materialized views that satisfy a smaller set
of queries more completely. The goal is to build generally applicable materialized views
rather than very specific ones. This is particularly important in the business intelligence
world, which is often characterized by ad hoc (unpredictable) queries.
The idea of “merging” for materialized views was first published in Agrawal et al
[2000]. This technique introduces new candidate materialized views that may not be
optimal for any individual statement in the workload, but may be optimal for the work-
load as a whole. A powerful technique to detect similarities between fragments of differ-
ent queries was described by Lehner et al. [2001], called mass query optimization or
more commonly multi-query optimizaton. In this paper the authors describe a tech-
nique to detect query execution plans that are beneficial to sets of queries, rather than
single statements. The combined query execution plans indirectly detect the commonal-
ities that are useful for materialized view candidates. Though Lehner et al. planned to
initially use this method for improving the performance of materialized view refresh, the
same techniques were then reused by them and their colleagues [Zilio et al. 2005] to use
the detected commonalities as suggestions for materialized views within the what-if
analysis of the DB2 Design Advisor.
