Neoview SQL Reference Manual (R2.4)

STDDEV, MIN, MAX, COUNT(*), and COUNT(x). The aggregate function can include
expression on the columns, such as SUM(a+b). Aggregate MVs can be of two types:
— MAV on Single Table: the MAV is defined on a single base table or MV.
— MAV on Explicit Join (MAJV): the MAV is defined on an inner equi-join of several base
tables, MVs, or both.
• RECOMPUTE materialized views: A materialized view that is initialized every time the
materialized view needs to be updated with changes to its base tables.
Authorization
Materialized views have the same security policies as regular views, which are independent of
those of its underlying tables.
The creator of a materialized view must have privileges both on the objects the view references
and for the schema. Privileges required to create the object include the SELECT privilege. To
grant a SELECT privilege on the materialized view to another user, the creator of the materialized
view must have the grant option on the underlying objects. In order to create a materialized view
in the schema, the creator must be the schema owner, or have the CREATE privilege.
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 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.
64 SQL Statements