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
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
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.