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

9
The IGNORE CHANGES ON clause can be used in these scenarios:
• If users do not want to change the materialized view to reflect changes to one or more tables,
they should specify the tables’ names in the IGNORE CHANGES ON clause. Typically, changes
to the dimension tables can be ignored to avoid restatement of history. However, new data
inserted into the fact table will be joined with the updated dimension tables.
• If users know in advance that the changes to be done to a table cannot affect the MV’s history
(e.g., the updated columns are not used by the MV), they can define the table with IGNORE
CHANGES ON.
• If the table is an INSERT-only table and the join predicate is based on a foreign key (enforced by
the database or by the application), users can define the table as IGNORE CHANGES ON and
guarantee the correctness of the MV.
Specifying the table’s name in the IGNORE CHANGES ON clause is an optimization hint. This
optimization hint prevents the overhead of the REFRESH operation associated with these changes. If
all the MVs on the table ignore changes on it, it also prevents logging. Every MV must have at least
one base table that does not appear in its IGNORE CHANGES ON clause.
INITIALIZE ON CREATE
The materialized view is initialized upon creation. The indices that are automatically created are also
populated upon creation. The materialized view is available for SELECT immediately after creation.
INITIALIZE ON REFRESH
The materialized view is not initialized when it is created. It is initialized the first time it is refreshed.
The MV is not available to SELECT until it is initialized.
When an MV is created with INITIALIZE ON REFRESH, all its system secondary indexes are created
as non-populated. As part of the initialization, the REFRESH operation populates all the non-populated
indexes, not only the indexes that were created by the system.
File options
File options for materialized views are a subset of the table file options. See the CREATE TABLE
statement for more information.
STORE BY (key-column-list)
The STORE BY clause specifies the order of rows within the physical file that holds the table, and
determines the physical organization of the table and the ways the user can partition the table. The
storage key is the clustering index. The user defines the clustering index for the MV table using the
column list. The key columns in the key-column-list must be NOT NULL columns from the MV query
expression.
All materialized views are automatically clustered and users are not required to include a STORE BY
clause when defining an MV. For more details refer to the Materialized views automatic clustering
section later in this document.
PARTITION BY key-column-list
In Neoview hash partitioning is the only partitioning scheme supported for MVs. This clause defines
the partitioning keys of the MV.