Neoview SQL Reference Manual (R2.4 SP2)

mv-attributes
The COMMIT REFRESH EACH attribute is only allowed for ON REQUEST MVs that are
defined on single delta MVs. A single delta MV is an MV that can be refreshed based on
changes to a single table. So, a MAV or a single delta MAJV (an MV that ignores updates for
all the tables in the FROM clause except one table) can specify the COMMIT REFRESH EACH.
n-rows refers to the number of rows that refresh processes from the log before committing
a transaction and starting another one. There is no recommended value for n-rows, but the
number must be less than 5000. The default value is zero (0), which means that the entire
refresh for the materialized view is done in one transaction.
Considerations for CREATE MATERIALIZED VIEW
• Materialized aggregate views do not support the DISTINCT clause as an argument of the
aggregate function. Additionally, aggregate functions as subqueries in an aggregate function
expression are not allowed.
• If optional column names for the materialized view are not specified, they default to the
column names (or derived names) specified in the materialized view query expression.
• You can specify GROUP BY using ordinals to refer to the relative position within the SELECT
list. For example, GROUP BY 3, 2, 1.
• ON REQUEST and ON STATEMENT materialized views cannot be created on views.
Types of Materialized Views
Neoview SQL defines the following three types of materialized views:
• Materialized Join View (MJV): An ON STATEMENT or ON REQUEST materialized view
holds the results of a join query with inner equi-joins of several tables. Outer joins and cross
joins are not supported. These tables can be base tables or other MVs. The query does not
include aggregations. A materialized view on a single table without an aggregate is also an
MJV.
• Materialized Aggregate View (MAV): An ON REQUEST materialized view holds the result
of a GROUP BY query with any of these aggregate functions: SUM, AVG, VARIANCE,
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. RECOMPUTE
materialized views can hold the result of any query that can be specified for a regular view.
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.
CREATE MATERIALIZED VIEW Statement 65