Neoview SQL Reference Manual (R2.4)

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.
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): A materialized join view that 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.
• Materialized Aggregate View (MAV): A materialized aggregate view that holds the result
of a GROUP BY query with any of these aggregate functions: SUM, AVG, VARIANCE,
CREATE MATERIALIZED VIEW Statement 63