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

10
MVATTRIBUTE COMMIT REFRESH EACH n-rows
This materialized view attribute is used to enhance the availability of the MV and its base tables.
Neoview SQL has restrictions that we have to consider when we refresh an ON REQUEST MV:
• If there are more than the TMF threshold locks per partition the partition is locked (lock escalation)
• The transaction might be aborted by TMF due to timeout (AUTOABORT) or audit trail size limits
When the REFRESH operation needs to handle very large amounts of data, these limitations may
become a problem. This is where a multi-transactional REFRESH can be used. Using the COMMIT
REFRESH EACH syntax instructs REFRESH to break the operation into multiple, smaller transactions.
After about n-rows rows have been read from the log, the transaction is committed, the work is
continued in a new transaction, and so on, until the REFRESH operation is complete.
This option is available only for ON REQUEST single-delta MAVs. A single-delta MV is an MV on a
single table or an MV with a join where only one of the base tables is not listed in the IGNORE
CHANGES clause. Such an MV is always available for querying; however, REFRESH operation
performance incurs a small overhead.
One consequence of breaking the REFRESH operation into multiple transactions is that in case of a
failure after the first transaction has been committed, the MV state may not be transactionally
consistent with the base tables. To recover from this state, the user must run the REFRESH operation
again. It will automatically perform the necessary recovery and complete the REFRESH operation.
Until REFRESH recovers from this state, the MV will remain locked for DDL operations, and cannot be
altered.
Query Expression
The query-expr is a subset of the query expression supported in a regular view definition. Only
expressions that start with SELECT are supported, and there are additional restrictions. Embedded
INSERT, UPDATE, and DELETE operations, as well as stream queries are not allowed as part of the
query expression.
Incremental Materialized View Restrictions
These restrictions specify which MV types can be defined as incremental:
MJV (Materialized Join View):
• Only inner equi-joins are supported. Outer joins and cross joins are not supported.
• Must be a single block SELECT-FROM-WHERE query (UNION and ORDER BY are not
allowed).
MAV (Materialized Aggregate View) on a Single Table:
• ON STATEMENT MAVs are not supported.
• The HAVING clause is not supported.
• The DISTINCT function is not supported.
• Aggregates must be topmost functions and they cannot contain nested aggregates, i.e., no
AVG(X)+1.
• The REFRESH of MAV with a MIN or MAX aggregate might become non-incremental if the
MIN or MAX value is deleted. See MIN and MAX support.
• Must be a single block SELECT-FROM-WHERE-GROUPBY query (e.g. ORDER BY is not
allowed). There is one exception: nested blocks are allowed when the inner block purpose is
to extract a group by column using a built-in function like substring or extract. For example: