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

7
Note that logging introduces a substantial overhead on the IUD (INSERT/UPDATE/DELETE) operation
to the base table. For INSERT and DELETE, this overhead can be as large as 90% compared to the
same operation when no logging is performed. For UPDATE, in Release 2.4 the performance is
substantially enhanced but the overhead can still be as high as 300%.
Maintaining an ON STATEMENT incremental MV does not require logging as the MV is updated in
the same transaction as the statement that updates any of its base tables. The ON STATEMENT MV
logic does however use an internal temporary table to help with maintaining this type of MV. The
temporary table is similar to that of the auxiliary table used to support the trigger implementation. A
table that is used by ON STATEMENT MVs will have a temporary table associated with it to support
maintaining ON STATEMENT MVs. The contents of this table are local to the transaction that is
maintaining the MV. In other words, all rows added to the table in the transaction are cleared prior to
the completion of the transaction.
REFRESH
The process of bringing an ON REQUEST MV up to date with its base tables is a REFRESH operation.
Refreshing materialized views in ON REQUEST mode is implemented as an extension to the
MAINTAIN command. This operation involves complex multi-transactional logic. The MAINTAIN
command can refresh either a single MV or a group of MVs that belong to an MVGROUP. When
refreshing an MVGROUP, the REFRESH operation arranges the tasks of refreshing the individual MVs
to keep the integrity of the data and take advantage of the inherent parallelism of the database.
ON STATEMENT MVs are automatically refreshed as part of the statement that updates any of its
base tables. The REFRESH operation is used on ON STATEMENT MVs only when the MV is defined
as INITIALIZE ON REFRESH and for this case the operation is only run once to initialize the MV.
Running the REFRESH operation on an ON STATEMENT MV in any other cases will result in an error.
Commands and Operations
This section describes commands added to Neoview SQL to support materialized views, as well as
syntax added to existing commands.
CREATE MATERIALIZED VIEW
A materialized view is a view. The MV name belongs to the same namespace as tables and regular
views. As a view, an MV has well-known semantics. There is one major difference between MVs and
regular views: MVs are not updateable. Users cannot update the underlying table by updating the
materialized view. Because MVs are materialized as tables, users can specify file options like location
and partitioning for them.
Creation of an MV may cause recompilation of all INSERT, UPDATE and DELETE compiled statements
that use its base tables.