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

Note that if an ON REQUEST MV is not refreshed, the data in the log tables of the base table is

accumulated and cannot be cleaned. This is also true for ON REQUEST MVs that were created but

have not been initialized.

REFRESH Operation Considerations

Since the REFRESH of a materialized view group may take a long time, the operation is broken into

separate transactions, generally one per MV. Successes and failures for each MV are reported to the

EMS log. The REFRESH operation ensures that after the refreshing transactions commit each refreshed

MV reflects a consistent database state.

Performance issues

Multiple-delta REFRESH

Refreshing a materialized view based on changes in multiple tables is an expensive operation since it

requires numerous joins. REFRESH performance strongly depends on the number of changed base

tables and may deteriorate exponentially as this number increases. Depending on the number and

size of the deltas, a multiple-delta refresh operation has the potential to be more expensive than

recomputing the MV. Furthermore, to guarantee consistency, during a multi-delta REFRESH the base

tables are locked in shared mode for the duration of the REFRESH operation.

For these reasons, users should be strongly advised to avoid multi-delta REFRESH operations.

These are several ways users can avoid these operations:

• The best way is to use the IGNORE CHANGES clause of the CREATE MV command. If users

know that they will not need to restate history for changes to the dimension tables, they can add

the names of those dimension tables to the IGNORE CHANGES clause. Only changes to the fact

table will be considered by the REFRESH operation.

• Another way is to use the NOMVLOG option when updating the dimension tables. INSERT,

UPDATE, and DELETE operations will not add the corresponding data to the log, and it will be

ignored by the REFRESH operation.

• The third way is to control the data loading process. In data warehouse environments customers

are in full control of the process of loading the updates to both the fact and dimensions tables.

Customers can load one table, perform a REFRESH, then load the next and perform a REFRESH

again. This process provides control of the number of deltas considered by each MV REFRESH.

For example, if history needs to be restated for a MAV, the best method is to load changes to

one of the tables, perform a REFRESH, load changes to another table, perform another REFRESH,

and so on. This method of running the REFRESH operation gives much better performance than

running a single REFRESH after loading changes to all tables.

Maintenance operations after a REFRESH

It is recommended that UPDATE STATISTICS and REORG maintenance operations be performed on

the MV after a refresh. These can be done using MAINTAIN. Here is an example of such an

operation:

Maintain mv mv-name, all;

For ON REQUEST MVs, REFRESH is more optimal when executed part of maintaining the base table

of the MV. In this case, maintain runs UPDATE STATISTICS on the MV IUD log prior to executing

REFRSH on the MV. Stats on the MV IUD log should improve the performance of the MV refresh.

Statistics on the MV IUD LOG can also be done explicitly but using non-externalized syntax.

Update statistics for LOG table <table-name> on every key;