Best Practices for Using Materialized Views in HP Neoview Release 2.4 (R2.4, R2.4 SP1, R2.5)

5
Overview
Materialized View REFRESH Policies
When an MV is updated incrementally to reflect changes in its base tables, we say that it is
incrementally refreshed. Not all materialized views can be incrementally refreshed. MVs that cannot
be refreshed incrementally have to be completely recomputed from the base tables and therefore their
maintenance costs are relatively high.
There are two incremental MV REFRESH modes:
ON REQUEST - explicit REFRESH
ON STATEMENT - immediate REFRESH.
In the explicit REFRESH mode, an MV is updated by an explicit REFRESH command. The MV REFRESH
operation is de-coupled from the transaction that updated the MV’s base table, and is deferred to a
time explicitly requested by the user. These MVs allow a high update rate of the underlying tables, but
the queries read data that only reflects the state of the base tables at the time of the last REFRESH. In
the explicit REFRESH mode, the only overhead on the updating transaction is the maintenance of the
logs, one for each base table, that record the changes to the base tables. The same log is used to
update all the MVs defined on a table, so this mode is scalable with respect to the number of such
MVs. Explicitly refreshed MVs can be used by queries that can tolerate stale data, e.g., queries that
are common in decision support applications.
This type of periodic REFRESH is widely used in data warehouse environments where most of the data
is updated periodically. Materialized views can be used as the tool for automatic data warehouse
maintenance.
In immediate REFRESH mode, the REFRESH of the MV is done as part of every transaction that
updates the base tables, just like a trigger. ON STATEMENT MVs are, therefore, always consistent
with their base tables. The price of this consistency is a performance penalty on the updating
transaction. An application uses ON STATEMENT MVs if the MV needs to be consistent with its base
tables at all times, and updates do not include large volumes of data.
For example, in a cellular billing application that has customers with restricted accounts, a balance-
due MV on call data might be critical in order to block calls, if necessary. Such an MV must be
immediately refreshed. However, immediate maintenance is not scalable with respect to the number
of MVs.
Types of Incremental Materialized Views
A materialized view is incremental if it can be refreshed based on changes to the underlying tables.
The only way to refresh non-incremental MVs is to recompute them. In Neoview, the following types of
MVs are incremental:
• Materialized Join View (MJV): A materialized join view holds the results of a join query with
inner equi-joins of several tables. Outer joins and cross joins are not supported. These tables can
be base tables or other MVs. The query does not include aggregations. Both ON REQUEST and
ON STATEMENT MJVs are supported. For example: