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

21
Once this option is used the MV will no longer be consistent with its base tables, and recomputing it
will produce a different result.
LOCK and UNLOCK
The LOCK TABLE and UNLOCK TABLE commands used to lock and unlock tables and views may also
be used on materialized views. Using the LOCK command on an MV locks the MV itself, rather than
the underlying tables.
Impact on Other Commands
Since materialized views are tables, users can reference them as such, as long as they do not use DDL
commands that alter the table definition. Using INSERT and UPDATE directly on an MV is not allowed
because it will break the MV’s consistency. However, DELETE on an ON REQUEST MV is allowed.
For example, suppose there is a database in which the fact table is very large. Because of disk space
restrictions, it is limited to last month’s data. On top of it are defined two MAVs: one holds a daily
summary of the past year, and the other holds a monthly summary for the entire history. The size of
the fact table can be maintained by deleting month-old data using the NOMVLOG option, and that of
the first MAV by deleting the year-old data directly.
Triggers or constraints cannot be defined on MVs.
Utilities
Support for materialized views has been added to these utilities:
PURGEDATA
o Purgedata can be used on base tables used by MVs. The NOMVLOG keyword is required.
Purgedata can also be used on MVs but, as of Release 2.4, parallel Purgedata is not supported.
TRANSFORM
o Transform can be used on both MVs base tables and MVs.
Privileges
MVs have the same security policies as regular views, which are independent of those of its
underlying tables.
The creator of an MV must have SELECT privileges on all the objects underlying the MV. To grant a
SELECT privilege on the MV to another user, the MV’s creator must have the grant option on the
underlying objects.
The INSERT privilege is used to authorize the incremental REFRESH. A user must have INSERT
privileges on an MV to be able to perform an incremental REFRESH on it.
Users must have full access (SELECT, INSERT, and DELETE privileges) to the MV to perform either
initialize or recompute on the MV. This requirement is inherited from other utilities like PURGEDATA
and POPULATE INDEX that REFRESH uses.