Neoview SQL Reference Manual (R2.5)

The INSERT privilege is used to authorize the incremental REFRESH. You must have INSERT
privileges on a materialized view to perform an incremental REFRESH on it. The INSERT privilege
can be at the object or schema level.
You must have full access (SELECT, INSERT, and DELETE privileges) to the materialized view
to perform either initialize or recompute on the materialized view.
The INSERT and DELETE privileges on a materialized view can be granted by the creator of the
materialized view, the schema owner, or any user that has a grant option. INSERT and UPDATE
commands are blocked on materialized views. The DELETE command on RECOMPUTE and
ON STATEMENT materialized views is blocked. The DELETE privilege on ON REQUEST
materialized view enables the user to perform deletes.
Materialized Views Clustering
Materialized views are automatically clustered as follows:
• MAVs and MAJVs — The clustering key defaults to the set of all not null GROUP BY columns.
• MJVs — The clustering key defaults to the materialized view SYSKEY and the clustering
columns of one of the base tables. If the materialized view is defined on a single table and
the table does not have a SYSKEY, the materialized view will not have a SYSKEY.
• RECOMPUTE — The clustering key defaults to the materialized view SYSKEY and the
subset of the clustering columns used by the materialized view of one of the base tables. If
the materialized view is defined on a single table and all the table's clustering columns are
used by the materialized view, the materialized view will not have a SYSKEY.
NOTE: SYSKEY is a storage key defined by the system rather than the user.
Materialized Views Using Other Materialized Views
When one materialized view uses other materialized views, their refresh types must be compatible.
You cannot create an ON STATEMENT materialized view that uses an ON REQUEST materialized
view or any RECOMPUTE materialized views. An ON REQUEST materialized view can use ON
STATEMENT materialized views but not RECOMPUTE materialized views. RECOMPUTE
materialized views can be defined on any other type of materialized view.
Single Delta Materialized Views
A materialized view is single delta if it is based on a single table (base table or another materialized
view) or on a join where all tables but one are in the IGNORE CHANGES list.
Indexes and Materialized Views
Secondary indexes may be created automatically by the system for incremental materialized
views. These indexes are designed to enhance the performance of the REFRESH operation.
For MJVs, the system-added secondary indexes are based on the underlying base tables’ clustering
index columns. These indexes are not created for underlying tables with INSERTLOG attribute
or those tables that are included in the materialized views' IGNORE CHANGES clause. Neoview
SQL tries to minimize the number of secondary indexes created by the system based on the equal
predicates in the query expression.
For MAVs, the clustering index includes all the NOT NULL group-by columns. If some of the
group-by columns are nullable, the system generates a secondary index based on all the group-by
columns.
Secondary indexes created by the system for incremental REFRESH of materialized views might
be expensive to maintain. Neoview SQL allows users to remove indexes that were created by
the system and to create alternative indexes. If you remove system-added indexes and do not
replace them with alternative indexes, the performance of the REFRESH operation may be greatly
hurt.
66 SQL Statements