Neoview SQL Reference Manual (R2.4 SP2)

Restrictions for CREATE MATERIALIZED VIEW
The following restrictions specify what materialized view types can be defined as incremental.
MJV (Materialized Join View)
• Only inner equi-joins are supported; outer joins and cross joins are not supported.
• Must be a single block SELECT-FROM-WHERE query (for example, UNION, ORDER BY,
subqueries, TRANSPOSE, and SAMPLE are not allowed).
• SELECT DISTINCT is not supported.
• Aggregates are not allowed in the select list.
MAV (Materialized Aggregate View)
• The MAV can also be defined on a previously defined MV.
• The HAVING clause is not allowed.
• The DISTINCT function is not supported.
• Aggregates must be top-most functions and they cannot contain nested aggregates (for
example, no AVG(X)+1).
• Under certain conditions when the MAX or MIN function is used in a MAV, the incremental
refresh of the MV returns an error and the MV needs to be recomputed. This is because if a
row that contains the MAX or MIN value of its group is deleted from the base table, a new
value cannot be incrementally computed. The failure of an incremental refresh operation of
a MAV, because of the deletion of the MIN or MAX value, can be avoided:
— if an even greater (or smaller) value was inserted, it is the new MAX (or MIN) value
— if the base table is in insert-only table, mark it as INSERTLOG
— add an index on the base table on the columns that are used as the MVs group by
columns. When such a supporting index exists, the new MAX or MIN value can be
computed without a full scan on the base table, keeping the REFRESH incremental.
If none of these options is used, the MV has to be recomputed.
• The columns in the GROUP BY clause cannot exceed the maximum key length limit.
• Must be a single block SELECT-FROM-WHERE-GROUPBY query (for example, ORDER
BY is not allowed). There is one exception to this rule; nested blocks are allowed when the
inner block’s purpose is to extract a group by column (using a built-in function like substring
or extract).
• Non-repeatable expressions (for example, current_time) are not supported for all types
of aggregate materialized views.
Examples of CREATE MATERIALIZED VIEW
• This is an example of an aggregate MAV:
CREATE MATERIALIZED VIEW PUBSCH.DETAIL_MV
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT PUBSCH.DETAIL_TABLE.ORDERNUM, SUM(PUBSCH.DETAIL_TABLE.QUANTITY)
AS TOTAL_AVAIL
FROM PUBSCH.DETAIL_TABLE
WHERE PUBSCH.DETAIL_TABLE.ORDERNUM > 1
GROUP BY PUBSCH.DETAIL_TABLE.ORDERNUM;
• This is an example of an MJV:
CREATE MATERIALIZED VIEW sales_store
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT price, partkey, timekey, store.nam
CREATE MATERIALIZED VIEW Statement 67