Neoview SQL Reference Manual (R2.4)

Syntax Description of CREATE MATERIALIZED VIEW
mv-name
specifies the ANSI logical name for the materialized view to create.
column-name-list
specifies names for the columns in the materialized view, as well as headings for the columns.
Column names in the list correspond directly to columns in the query-expr. If you omit
this clause, columns in the materialized view will have the same names as the corresponding
columns in the query-expr. If a column in query-expr does not have an implicit name
(for example, a function) and an explicit name is not specified using the AS syntax, the
column-name-list must be specified or an error is returned. You must specify this clause
if any two columns in the tables specified by query-expr have the same name.
column-name-list is optional.
You can specify GROUP BY using ordinals to refer to the relative position within the SELECT
list. For example, GROUP BY 3, 2, 1.
INITIALIZE ON CREATE
implies that the materialized view gets its initial content upon its creation. A materialized
view that is based on another materialized view can be initialized on create only if the query
expression does not include joins and if the underlying materialized view is already initialized.
This restriction is required in order to guarantee the consistency of the materialized view.
INITIALIZE ON REFRESH
implies that the materialized view gets its initial content on its first refresh.
refresh-type
specifies the method that will be used to update the materialized view.
ON REQUEST
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. At least one table from the FROM
clause of the MV should not appear 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)
62 SQL Statements