Neoview SQL Reference Manual (R2.4 SP2)

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 MV
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 MV 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 MV 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.
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 MVs. 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 MV's 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 MV 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.
You can create additional secondary indexes, which cannot be unique.
Joins
• An inner equi-join query is a query where rows from each table are matched to specific rows
in other tables using equal predicates.
• Outer joins include LEFT OUTER JOIN or FULL OUTER JOIN.
• In a cross join, not all predicates are given so each row of a table is matched with all the rows
of the other table.
66 SQL Statements