Chapter 2. DB2 UDB’s materialized views 17
2.1.2 Materialized view concept overview
Figure 2-1 provides an overview of the materialized view concept.
Figure 2-1 Materialized view overview
Support for materialized views requires the following:
? Having a DBA
pre-compute an aggregate query, and materialize the results
into a table. This summary table would contain a superset of the information
that could answer a number of queries that had minor variations.
? Enhancing the DB2 optimizer to
automatically rewrite a query against the
base tables, to target the materialized view instead (if appropriate) in order to
satisfy the original query.
Since the materialized view often contains precomputed summaries and/or a
filtered subset of the data, it would tend to be much smaller in size than the base
tables from which it was derived. When a user query accessing the base table is
automatically rewritten by the DB2 optimizer to access the materialized view
instead, then significant performance gains can be achieved.
DB2 Optimizer
SQL Queries
Against Base Tables
Base Table
T2
materialized views
T1,T2,..Tn
Base Table
Tn
Base Table
T1
Immediate
Refresh
Deferred
Refresh
with
query
rewrite
no
query
rewrite
OR
18 High-Function Business Intelligence in e-business
Figure 2-1 also shows that two approaches may be adopted to maintain the
materialized view:
? In the deferred refresh approach, DB2 does not automatically keep the
materialized view in sync with the base tables, when the base tables are
updated. In such cases, there may be a latency between the contents of the
materialized view, and the contents in the base tables.
? In the immediate refresh approach, the contents of the materialized view are
always kept in sync with the base tables. An update to an underlying base
table is immediately reflected in the materialized view as part of the update
processing. Other users can see these changes after the unit of work has
completed on a commit. There is no latency between the contents of the
materialized view and the contents in the base tables.
The pros and cons of each approach are discussed in detail in 2.3, “Materialized
view maintenance considerations” on page 26.
Case Study: In one customer scenario, a query required computing the total
sales for all product categories for the year 1998. It involved joining a 1.5
billion row transaction table with three dimensional tables. The query had to
touch at least 400 million rows in the transaction table.
Without an materialized view, the response time on a 22-node SP was 43
minutes.
With an materialized view, the response time was reduced to 3 seconds!!!
In this case, DB2 touched at least 4000 times fewer rows and avoided a join.
The benefits achievable with materialized views depends upon one’s
own unique workload, and your mileage will vary.
Important: Materialized view functionality is somewhat similar to the role of a
DB2 index which provides an efficient access path that the query user is
typically unaware of. However, unlike an index, a user may directly query the
materialized view, but this is not generally recommended since it would detract
from the appeal of an materialized view being a black box that an
administrator creates and destroys as required to deliver superior query
performance.
Adapting their queries to use a materialized view may not be a trivial exercise
for the user.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.