60 High-Function Business Intelligence in e-business
2.8 Materialized view design considerations
Materialized views have the potential to provide significant performance
enhancements to certain types of queries, and should be a key tuning option in
every DBA’s arsenal.
However, materialized views do have certain overheads which should be
carefully considered when designing materialized views. These include:
? Disk space due to the materialized view and associated indexes, as well as
staging tables.
? Locking contention on the materialized view during a refresh.
– With deferred refresh, the materialized view is offline while the REFRESH
TABLE is executing.
• The same applies to staging table if one exists. Update activity against
base tables is impacted during the refresh window.
– With immediate refresh, there is contention on the materialized view when
aggregation is involved due to SQL insert, update and delete activity on
the base table by multiple transactions.
? Logging overhead during refresh of very large tables.
? Logging associated with staging tables.
? Response time overhead on SQL updating the base tables when immediate
refresh and staging tables are involved, because of the synchronous nature of
this operation.
When a materialized view has many tables and columns in it, it is sometimes
referred to as a “wide” materialized view. Such a materialized view allows a larger
portion of a user query to be matched, and hence provides better performance.
However, when the query has fewer tables in it than in the materialized view, we
need to have declarative or informational referential integrity constraints defined
between certain tables in order for DB2 to use the materialized view for the query
as discussed in 3., “Extra tables in the query:” on page 52.Note that a potential
disadvantage of “wide” materialized views is that they not only tend to consume
more disk space, but may also not be chosen for optimization because of the
increased costs of accessing them.
Important: The objective should be to minimize the number of materialized
views required by defining sufficiently granular REFRESH IMMEDIATE and
REFRESH DEFERRED materialized views that deliver the desired
performance, while minimizing their overheads.
Chapter 2. DB2 UDB’s materialized views 61
When a materialized view has fewer columns and/or tables, it is sometimes
referred to as a “thin” materialized view. In such cases, we reduce space
consumption at the cost of performing joins during the execution of the query. For
example, we may want to only store aggregate information from a fact table (in a
star schema) in the materialized view, and pick up dimension information from
the dimension tables through a join. Note that in order for DB2 to use such a
materialized view, the join columns to the dimension tables must be defined in
the materialized view. Note also that referential integrity constraints requirements
do not apply to “thin” materialized views.
Figure 2-10 provides an overview of the steps involved in designing REFRESH
DEFERRED materialized views.
Attention: We will only be focusing on designing REFRESH DEFERRED
materialized views, since the data warehouse environment is the predominant
opportunity for exploiting materialized view optimization, and the data
warehouse environment tends to overwhelmingly require access to other than
current data.
62 High-Function Business Intelligence in e-business
Figure 2-10 Overview of the design of REFRESH DEFERRED materialized views
LOAD production data
S3
Reset CHECK PENDING NO ACCESS state if appropriate, execute RUNSTATS, and EXPLAIN the query
Collect all relevant queries, and prioritize them by importance
Is the MV used by the query?
Yes
End of queries?
No
Existing MV suitable?
Generalize local predicates to GROUP BY and design the materialized view (MV)
Are the performance gains satisfactory?
No
No
Consider each query in turn
Size acceptable?
No
Yes
Reduce MV size through splits or
more predicates
No
Modify the MV to suit
Yes
Yes
Create a new MV
Review matching criteria, modify query as required and retry query
Consolidate MV's with only few matching queries, keeping size in mind
Yes
Existing MV modifiable to suit?
No
Create indexes, update MV's with PRODUCTION statistics (MV NOT populated), and EXPLAIN all the queries
Review MV design
issues and reiterate
Yes
Queries still use the MV's?
No
Yes
Execute queries and measure performance with and without MV optimization, and extrapolate performance to production data
Create & populate "miniature" base tables, and MV's with SAMPLE data, and execute RUNSTATS
Cost issue -- may or
may not be a problem.
Further investigation
needed.
S1
S9
S2
S5
S6
S7
S8
S4
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.