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

Materialized views defined on top of other Materialized views

restrictions

Materialized views can use other materialized views as base tables, but not all refresh type

combinations are allowed. The restriction on refresh type combination is put in place to ensure

materialized view consistency. The following compatibilities are noted:

• An ON STATEMENT materialized view cannot be defined on top of an ON REQUEST or a

RECOMPUTE materialized view. An ON STATEMENT materialized view can be defined on

top of other ON STATEMENT materialized views.

• An ON REQUEST materialized view can be defined on top of an ON STATEMENT or an ON

REQUEST materialized view. An ON REQUEST materialized view cannot be defined on top

of a RECOMPUTE materialized view.

• A RECOMPUTE materialized vie can be defined on top of any other type of materialized

views.

A materialized view that is based on another materialized view can be defined as INITIALIZE ON

CREATE only if:

• The materialized view query expression does not include joins

• The underlying materialized view is already initialized

Neoview SQL identifies some optimization opportunities when an MV is defined on another MV. The

delta pipelining optimization is used when the delta of one MV can be pipelined in memory to refresh

the other MV that consumes this delta and therefore the delta need not be materialized. This

optimization can be significant if the delta is large. This optimization can be used only if the following

conditions are met:

• All the MVs that consume the delta are refreshed together with the delta producer (through an

MVGROUP if several consumers).

• The MVs that consume the delta are not consumers of other deltas. In other words, if one of

the consumers is defined on a join, the MV part of the join is the only table that is changing

or if others are also changing they are only in the IGNORE CHANGES list of the consumer.

• All the MVs are aggregate views.

Automatic Log Maintenance

In Release 2.4, Neoview introduced the “Automatic Loggable Columns Maintenance” feature. With

this feature, only columns required to support MVs defined on a base table are represented in the

base table IUD log. Because only a subset of the base table columns are logged, the Automatic Log

Maintenance feature improves performance of both MV logging and REFRESH operations as well as

reduce storage necessary for MV maintenance.

The Neoview log maintenance logic is carried out when materialized views are created or dropped.

At that point Neoview can decide what columns can be added or dropped from the IUD log. The

actual adding or dropping of columns is done by recreating the log from scratch. This can only be

done when the IUD log is empty. Therefore it is highly recommended that whenever an MV is created

or dropped, the MVs on its base tables will first be refreshed, so that their logs will be empty.