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

8
mv-name
The name of the MV must be unique, and must not be the name of any other MV, table, view or
stored procedure.
column-name-list
This optional clause specifies names for the columns in the MV, as well as headings for the columns.
Column names in the list correspond directly with columns in the query-expr. If the user omits this
clause, columns in the MV will have the same names as the corresponding columns in query-expr. If
a column in query-expr does not have an implicit name (e.g. if it is a function) and the user does not
specify an explicit name using the AS syntax, they must specify the column-name-list, or an error will
be returned. The user must specify this clause if any two columns in the tables specified by query-expr
have the same name.
RECOMPUTE
RECOMPUTE materialized views are totally re-evaluated every time they need to be synchronized
with their base tables.
REFRESH ON STATEMENT
ON STATEMENT materialized views are automatically refreshed as part of the statement that updates
any of the materialized view base tables.
REFRESH ON REQUEST
ON REQUEST materialized views are explicitly refreshed with the REFRESH operation of the
MAINTAIN command.
IGNORE CHANGES ON
This option allows a materialized view to ignore updates performed on any of its base tables, in the
sense that the updates do not affect the data that already exists in the MV. The tables referenced in
the changes clause must be regular base tables used directly by the MV (MVs cannot appear in this
clause). This clause is applicable only for ON REQUEST MVs.
CREATE MATERIALIZED VIEW mv
-
name
[column-name-list]
{RECOMPUTE | REFRESH refresh-type}
INITIALIZE ON initialization-type
[file-options]
[MVATTRIBUTE COMMIT REFRESH EACH n-rows]
AS query-expr
column-name-list:
( column-name [ heading] [, column-name [ heading]]...)
refresh-type:
ON STATEMENT | {ON REQUEST [changes]}
Initialization-type:
REFRESH | CREATE
changes:
IGNORE CHANGES ON table-name [,table-name]…
file-options is:
[STORE BY (key-column-list)]
[PARTITION BY key-column-list]