Neoview SQL Reference Manual (R2.5)

CREATE MATERIALIZED VIEW Statement
• “Syntax Description of CREATE MATERIALIZED VIEW”
• “Considerations for CREATE MATERIALIZED VIEW”
• “Examples of CREATE MATERIALIZED VIEW”
The CREATE MATERIALIZED VIEW statement creates a materialized view. See “Materialized
Views” (page 281).
CREATE {MATERIALIZED VIEW | MV} mv-name
[column-name-list]
{ RECOMPUTE | REFRESH refresh-type }
{ INITIALIZE ON REFRESH | INITIALIZE ON CREATE }
file-options
AS query-expr
column-name-list is:
(column-name [, column-name]...)
refresh-type is:
ON STATEMENT
| {ON REQUEST [ignore-changes]}
ignore-changes is:
IGNORE CHANGES ON simple-table [, simple-table]..
file-options is:
[clustering-def] [partition-definition] [mv-attributes]
clustering-def is:
STORE BY (key-column-list)
partition-definition is:
HASH PARTITION [BY key-column-list]
mv-attributes is:
MVATTRIBUTE[S] COMMIT REFRESH EACH
Syntax Description of CREATE MATERIALIZED VIEW
mv-name
specifies the ANSI logical name for the materialized view to create. See “Database Object
Names” (page 242).
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.
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.
CREATE MATERIALIZED VIEW Statement 63