Neoview SQL Reference Manual (R2.4)

Indexes and Materialized Views
Secondary indexes may be created automatically by the system for incremental MVs. These
indexes are designed to enhance the performance of the REFRESH operation.
For MJVs, the system-added secondary indexes are based on the underlying base tables’ clustering
index columns. These indexes are not created for underlying tables with INSERTLOG attribute
or those tables that are included in the MV's IGNORE CHANGES clause. Neoview SQL tries to
minimize the number of secondary indexes created by the system based on the equal predicates
in the query expression.
For MAVs, the clustering index includes all the NOT NULL group-by columns. If some of the
group-by columns are nullable, the system generates a secondary index based on all the group-by
columns.
Secondary indexes created by the system for incremental REFRESH of MV might be expensive
to maintain. Neoview SQL allows users to remove indexes that were created by the system and
to create alternative indexes. If you remove system-added indexes and do not replace them with
alternative indexes, the performance of the REFRESH operation may be greatly hurt.
You can create additional secondary indexes, which cannot be unique.
Joins
• An inner equi-join query is a query where rows from each table are matched to specific rows
in other tables using equal predicates.
• Outer joins include LEFT OUTER JOIN or FULL OUTER JOIN.
• In a cross join, not all predicates are given so each row of a table is matched with all the rows
of the other table.
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
are not allowed).
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.
CREATE MATERIALIZED VIEW Statement 65