Best Practices for Using Materialized Views in HP Neoview Release 2.4 (R2.4, R2.4 SP1, R2.5)

12
Note that COUNT(expr) is avoided when expr is based only on NOT NULL columns. In this case,
COUNT(*) is used instead.
MIN and MAX support
When the MAX or MIN function is used in a MAV, incremental REFRESH cannot always be
guaranteed. 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. Sometimes there is a workaround; for
example in cases where an even greater value was inserted, it is the new MAX or MIN value.
However, in most cases there is no alternative but to recompute the MV.
These recommendations may help:
• If the base table is an 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.
Indexes on MVs
Users can define the clustering index (store by clause) for MVs or take advantage of the automatic
clustering feature. Additional 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 incremental 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. The recommended store-by clause for incremental MJVs should include the MV
columns that are based on the clustering index columns of at least one of the underlying base tables.
For incremental MAVs, the automatically generated 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. The system adds the missing NOT NULL group-by columns at the
end of the store-by clause defined by the user.
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 users remove system-added indexes and do not replace them with alternative
indexes this may greatly hurt the performance of the REFRESH operation.
Users can create additional secondary indexes, which cannot be unique.
Materialized views automatic clustering
Materialized aggregate views (MAVs and MAJVs) are automatically clustered by Neoview. The
default clustering key is composed of all the non-null GROUP BY columns. If at least one of the
GROUP BY columns is nullable, a SYSKEY is appended to the list of the not nullable GROUP BY
columns to form the clustering index. The materialized view is partitioned by the clustering key
columns not including SYSKEY. If all of the GROUP BY columns are nullable then the materialized
view will be clustered by SYSKEY and will have a single partition.