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

13
Materialized join views (MJVs) are automatically clustered by Neoview. The default clustering key is
composed of the clustering key of one of the materialized view base tables and a system generated
SYSKEY column. Clustering columns of the base table with the largest size is chosen as the clustering
key.
MJVs that are defined on a single table and the table has a non-droppable primary key will not
include a SYSKEY in their clustering columns and will have a primary key. The primary key is
composed of the MV columns that correspond to the source table primary key columns.
As of Release 2.4, Neoview added support to automatically cluster RECOMPUTE materialized views.
Neoview continues to support user specified clustering clauses. Users are encouraged to provide a
clustering clause that works best with their expected workload. The automatic clustering of
RECOMPUTE materialized views implies that there are four ways such an MV can be clustered:
• User has specified a STORE BY or a HASH(2) PARTITION BY clause
The materialized view will be clustered using the user specified clustering columns plus a
SYSKEY. If only a HASH(2) PARTITION BY clause is specified, the materialized view will
be clustered by the partitioning columns plus a SYSKEY.
• The materialized view is defined on a single table and source table has a non-droppable
primary key and all table (Clustering Index) CI columns are used by the MV
The materialized view will have a primary key that is composed by the table/MV
clustering columns and will not include a SYSKEY.
• At least one of the tables the materialized view is defined on, has one or more of its
clustering index columns used by the MV
The materialized view is clustered by the table clustering columns plus a SYSKEY.
• None of the tables used by the materialized view has one of its clustering columns used by
the materialized view
The materialized view is a single partition MV clustered by SYSKEY.
Materialized Views and Partitioning
Neoview SQL uses special algorithms to partition and decide on the size of a materialized view
underlying table. These algorithms operate as follows:
• The materialized view number of partitions is the same as that of the largest table used by the
materialized view.
• The materialized view extent size is 50% that of the largest table used by the materialized
view.
• For tables that have ON REQUEST materialized views defined on them (and at least one of
them does not have the table in its IGNORE CHANGES clause), the IUD log table is co-
located with the base table.
• For tables that have ON STATEMENT materialized views defined on them, the auxiliary temp
table created to support these materialized views is a 4-way hash partitioned table (as of
Release 2.4, the temp table is 1-way partitioned).
• MAVs with no GROUP BY clauses produce a single row result, and are therefore not
partitioned.