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

The REFRESH Operation

REFRESH is implemented as a utility rather than an SQL command because of its complex multi-

transactional logic which cannot be implemented in a single dataflow tree. The REFRESH utility can be

applied to both incremental MVs and recomputed MVs. The REFRESH utility applies only the base

tables’ changes that were encountered since the last MV REFRESH to incremental MVs.

The REFRESH utility is designed to optimize the refresh of an MVGROUP. It is recommended that users

gather all the MVs they wish to refresh at a one time into a group and REFRESH them together.

Since the utility is multi-transactional, the refresh of some MVs in a group may fail. The utility reports

all failures to the EMS log. In case of partial failure, users can either REFRESH again the whole MV

group, or specific MVs. If the base tables did not change since the last attempt to REFRESH (i.e.,

classical data warehouse during the night), no performance penalty will be incurred for MVs that

were successfully refreshed before. In an environment where updates happen continuously, all MVs

will be affected by the second REFRESH. Refreshing individual MVs out of a group context may lead

to a state where MVs in the group are not in sync with each other.

RECOMPUTE

The RECOMPUTE option allows the users to explicitly recompute an incremental MV or all the

members of an MVGROUP.

CANCEL

This option is added to help recover from failure situations. See Failure Handling for details.

CASCADE

The cascade clause is relevant only for the REFRESH of a single MV that relies on other MVs. If the

CASCADE clause is specified, the REFRESH utility creates a virtual group that includes the current MV

and all the MVs that it uses (directly and indirectly), and refreshes them in the right order.

Refreshing a single MV that uses other MVs without the CASCADE option is possible for single-delta

MVs. The CASCADE option is not relevant in the REFRESH of a group since a group must include all

the MVs that any MV in the group that is not single-delta depends on.

MVLOG CLEANUP

The MVLOG CLEANUP option gives the user control over cleanup of the table logs of entries that are

no longer needed. The default option is WITH MVLOG CLEANUP which specifies that the logs should

be cleaned up at the end of the REFRESH. If users wish to execute the log cleanup outside the

REFRESH window, they should specify WITHOUT LOG CLEANUP to improve REFRESH performance,

then run the utility again outside the REFRESH window, with DO ONLY MVLOG CLEANUP.

MAINTAIN MV <mv

name>, REFRESH [CASCADE] <mv

refresh

option>

MAINTAIN MVGROUP <mvgroup-name>, REFRESH <mv-refresh-option>

MAINTAIN TABLE <table-name>, ALL

mv-refresh-option :

[RECOMPUTE | CANCEL]

[{WITH | WITHOUT | DO ONLY} MVLOG CLEANUP]