Neoview SQL Reference Manual (R2.3)

CREATE MATERIALIZED VIEW Statement
• “Syntax Description of CREATE MATERIALIZED VIEW”
• “Considerations for CREATE MATERIALIZED VIEW”
• “Example of CREATE MATERIALIZED VIEW”
The CREATE MATERIALIZED VIEW statement creates a materialized view.
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 n-rows
query-expr is:
SELECT column-expr [, column-expr]...
FROM table-ref [,table-ref] ...
[WHERE search-condition]
[GROUP BY {colname | colnum}[, {colname | colnum}]...]
table-ref is:
simple-table | joined-table
simple-table is:
{base-table-name | materialized-view-name}
[[AS] corr-name [(col-expr-list)]]
joined-table is:table-ref [NATURAL] [INNER] join table-ref [join-spec]
join-spec is:
ON search-condition
column-expr is:
{non-aggregate-column-expr [[AS] derived-name] |
aggregate-name(expr) [AS] derived-name}
aggregate-name is:
AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE
Syntax Description of CREATE MATERIALIZED VIEW
mv-name
specifies the ANSI logical name for the materialized view to create.
60 SQL Statements