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

DROP MATERIALIZED VIEW

This statement drops the MV. Users may not drop an MV that is used by other MVs or by regular

views without the CASCADE option, in which case they will be dropped as well. The drop command

removes the MV from all the MVGROUPs to which it belongs.

It may cause recompilation of SQL statements that use this MV, as well as any of its base tables.

MVGROUP Commands

An MVGROUP is a group of MVs that are refreshed together. One way to group MVs is by the

frequency of their REFRESH (e.g. each day, each week, each month). Another way is to create groups

to preserve consistency, that is, to group all the MVs on common tables. ON STATEMENT MVs

cannot be part of an MVGROUP.

Any RECOMPUTE MV can be added to an MVGROUP. However, an ON REQUEST MV can be

added to an MVGROUP only when it satisfies at least one of these conditions:

• All the MVs that are used by it (directly or indirectly) are also in the group

• The MV is single-delta

This restriction ensures that every MV in the group is consistent with a well-defined state of the

database tables after a REFRESH. A single-delta MV is defined as an ON REQUEST MV that can be

refreshed based on changes (delta) to a single table. A single-delta MV must satisfy this requirement:

• The MV is based either on a single table (base table or MV), or on a join where only one of the

base tables is not included in the IGNORE CHANGES clause.

Starting from Neoview Release 2.0, MVGROUPs are automatically created for every table used by

MVs. Those MVGROUPs have the same name as their corresponding base table. As MVs are

created, they are automatically added to the MVGROUP of their corresponding base tables. To

refresh all the MVs on a specific base table, use this command:

As of Neoview Release 2.2, users may also create their own MVGROUPs.

MAINTAIN MVGROUP <base

table

name>, REFRESH <mv

refresh

option>

CREATE MVGROUP mv_group

DROP MVGROUP mv_group

ALTER MVGROUP mv_group

{ ADD mv_name [,mv_name].. | REMOVE mv_name [,mv_name].. }

DROP MATERIALIZED VIEW mv

name [CASCADE]