Neoview SQL Reference Manual (R2.4 SP2)

refresh-type
specifies the method that will be used to update the materialized view.
ON REQUEST
specifies that the materialized view has to be explicitly refreshed through the MAINTAIN
command.
RECOMPUTE
a materialized view that is initialized every time the materialized view needs to be updated
with changes to its base tables.
ON STATEMENT
a materialized view that is refreshed ON STATEMENT is often called immediate. An ON
STATEMENT materialized view is maintained automatically as part of the statement that
updated any of the base tables that are part of the materialized view, just like indexes.
Therefore, once initialized, materialized views are always fully consistent with the database
base tables but are expected to slow down the update transactions. Only ON STATEMENT
materialized joined views are supported. ON STATEMENT materialized aggregate views
and ON STATEMENT MAV on Explicit Join (MAJV) are not supported.
ignore-changes
The IGNORE CHANGES ON clause instructs the refresh operation of a materialized view
over several base tables to ignore the changes to the listed base tables. This clause is applicable
only to ON REQUEST MAJVs and ON REQUEST MJVs. Make sure at least one table in the
FROM clause of the materialized view is not included in the IGNORE CHANGES ON clause.
Also, only base tables from the FROM clause of the MV can appear in the IGNORE CHANGES
ON clause. The IGNORE CHANGES list cannot include MVs.
file-options
The STORE BY and PARTITION BY clauses are optional for all types of materialized views.
ON REQUEST, RECOMPUTE, and ON STATEMENT materialized views are automatically
clustered and partitioned by Neoview.
STORE BY (key-column-list)
The STORE BY clause specifies the order of rows within the physical file that holds the table,
determines the physical organization of the table, and the ways you can partition the table.
The storage key is referred to as the clustering index. You define the clustering index for the
materialized view table using the column list. The key columns in the key-column-list
must be NOT NULL columns from the materialized view query expression.
HASH PARTITION BY (key-column-list)
Hash partitioning is the only partitioning scheme supported for materialized views. This
clause defines the partitioning keys of the materialized view.
column-expr
column-expr is a single column name or a derived column. A derived column
(derived-name) is an SQL value expression; its operands can be numeric, string, datetime,
or interval literals, columns, functions defined on columns, scalar subqueries, CASE
expressions, or CAST expressions. Any single column name in column-expr must be from
tables or views specified in the FROM clause.
aggregate-name
Specifies the aggregate name: AVG, COUNT, MAX, MIN, STDDEV, SUM, or VARIANCE.
query-expr
is a subset of the query expression supported in a regular view definition. Only expressions
that start with SELECT are supported. Restrictions include: no [FIRST n] or [ANY n]
specification, no order-by-clause, no access-mode clause, no STREAM access, and no
embedded inserts, updates, or deletes. For the syntax and syntax description of the
order-by-clause, see “SELECT Statement” (page 163).
64 SQL Statements