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

Failure Handling

REFRESH uses several mechanisms to ensure the consistency of the operation. One is the DDL lock,

which is used to disallow any DDL operations on the MV and its base tables for the duration of the

REFRESH operation. In case of a failure during a REFRESH, the DDL lock stays in effect and must be

handled during recovery. After a failure of the REFRESH operation, users have two options:

• Rerun the REFRESH operation. It will recover from the failure and continue where it last failed.

• Use REFRESH CANCEL to clear the DDL locks, then selectively REFRESH only the MVs that

failed.

The REFRESH CANCEL operation behaves differently based on the MV type:

Single transaction MVs

The cancel operation removes unnecessary locks. Since the MV is audited the data in it is correct and

represents the database state before or after the REFRESH. The users can use REFRESH to update the

MV.

Multi-transaction Materialized Views (that use the COMMIT REFRESH EACH clause)

The cancel operation removes unnecessary locks from the MV underlying tables. The DDL lock on the

MV remains. DLL operations on an MV that is in the middle of multi-transaction REFRESH are not

allowed. A REFRESH operation on these MVs completes the REFRESH. At the end of the operation the

MV reflects all the updates that were done to the tables up to the beginning of the REFRESH. If the

REFRESH operation fails again, it can be recovered with an additional REFRESH.

New Table Attributes

These new attributes can be specified in CREATE TABLE and ALTER TABLE commands:

INSERTLOG

This attribute is used to optimize the REFRESH operation. When the INSERTLOG attribute is specified

for a table, its log will include only records of inserts. DELETE and UPDATE operation are not logged,

and will be ignored by the ON REQUEST MVs that are defined on the table, as if they had a

NOMVLOG clause. Users can change the value of this attribute only if the log is empty or does not

exist. Changing this attribute in ALTER TABLE may cause recompilation of statements that perform

INSERT, UPDATE, or DELETE operations on the table. The default value is NO INSERTLOG.

NOMVLOG Option for INSERT, UPDATE, and DELETE DML Operations

This is a new option for INSERT, UPDATE and DELETE operations.

When the NOMVLOG option is specified for an operation, that operation is not recorded in the log

table. A typical use for this option is when the size of the fact table is constrained to a time period, for

example, one month, but the MAV on top of it must retain the data for much longer. Older data can

be deleted periodically from the fact table without affecting the MVs using it, which will continue to

reflect that data. The option is a NOOP if the table does not have a log.

DELETE [NOMVLOG] FROM …

UPDATE [NOMVLOG] <table-name> …

INSERT [NOMVLOG] INTO <table-name> …

[

NO INSERTLO

|INSERTLOG]