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

ALTER MATERIALIZED VIEW

This statement allows users to rename the MV, modify the COMMIT REFRESH EACH or the IGNORE

CHANGES clauses.

COMMIT REFRESH EACH <n-rows>

This clause is used to enable Multi-transactional REFRESH. <n-rows> specifies the number of rows

read from the IUD log in each transaction. The feature is disabled if the value of <n-rows> is set to 0.

This option is available only for ON REQUEST self maintainable MVs (e.g. MAV on a single table).

The refresh will be broken into short transactions. Such MV is always available for querying. The

intermediate state of the MV during the refresh might not reflect the database state at any moment of

time. The transactions will be based on the table-clustering key. Each transaction will represent the

changes done to several clustering key values. The refresh will read n-rows from the log and then start

to look for a clustering key value boundary. After a value boundary is detected, these rows are

applied to the MV and the transaction is committed. Notice that breaking the refresh to smaller

transactions might cause some performance deterioration since every operation is done on a smaller

number of rows.

IGNORE CHANGES

The ignore changes list of a materialized view can be altered by adding/removing base tables

to/from the materialized view IGNORE CHANGES list.

When can the IGNORE CHAGES list of a materialized view be altered?

• A base table of an MV can be added to the IGNORE CHANGES list by altering the MV

using the ADD IGNORE CHANGES ON clause.

• The ALTER MV to add ignore changes on a base table will be successful only if all the

updates to the base table up to that point are propagated to the MV by using refresh.

• If there are updated rows on a base table that are not used by refresh to update rows in the

MV then an error would be raised indicating that a refresh needs to be performed before

altering the MV to add ignore changes on the base table.

• Only a base table that is in the FROM clause of the query expression of the MV can be

added to the IGNORE CHANGES list.

• A base table of the MV that is already in the IGNORE CHANGES list can be removed from

the IGNORE CHANGES list by altering the MV using the REMOVE IGNORE CHANGES ON

clause.

ALTER MATERIALIZED VIEW mv

name mv

alter

action

mv-alter-action is:

MVATTRIBUTE[S] mv-attribute

| {ADD | REMOVE} IGNORE CHANGES ON table-name [,table-name]..

| RENAME TO new-mv-name

mv-attribute:

COMMIT REFRESH EACH nrows