About this task
Materialized query tables are a powerful way to improve
response time for complex queries, especially queries that might
require some of the following operations:
- Aggregated data over one or more dimensions
- Joins and aggregates data over a group of tables
- Data from a commonly accessed subset of data
- Repartitioned data from a table, or part of a table, in a partitioned
database environment
Here are some of the key restrictions regarding staging
tables:
- The query used to define the materialized query table, for which
the staging table is created, must be incrementally maintainable;
that is, it must adhere to the same rules as a materialized query
table with an immediate refresh option.
- Only a deferred refresh can have a supporting staging table. The
query also defines the materialized query table associated with the
staging table. The materialized query table must be defined with REFRESH
DEFERRED.
- When refreshing using the staging tables, only a refresh to the
current point in time is supported.
- Partitioned hierarchy tables and partitioned typed tables are
not supported. (Partitioned tables are tables where data is partitioned
into multiple storage objects based on the specifications provided
in the PARTITION BY clause of the CREATE TABLE statement.)
An inconsistent, incomplete, or pending state staging
table cannot be used to incrementally refresh the associated materialized
query table unless some other operations occur. These operations
will make the content of the staging table consistent with its associated
materialized query table and its underlying tables, and to bring the
staging table out of pending. Following a refresh of a materialized
query table, the content of its staging table is cleared and the
staging table is set to a normal state. A staging table might also
be pruned intentionally by using the SET INTEGRITY statement with
the appropriate options. Pruning will change the staging table to
an inconsistent state. For example, the following statement forces
the pruning of a staging table called STAGTAB1:
SET INTEGRITY FOR STAGTAB1 PRUNE;
When
a staging table is created, it is put in a pending state and has an
indicator that shows that the table is inconsistent or incomplete
with regard to the content of underlying tables and the associated
materialized query table. The staging table needs to be brought
out of the pending and inconsistent state in order to start collecting
the changes from its underlying tables. While in a pending state,
any attempts to make modifications to any of the staging table's
underlying tables will fail, as will any attempts to refresh the associated
materialized query table.
There are several ways a staging
table might be brought out of a pending state; for example:
- SET INTEGRITY FOR <staging table name> STAGING IMMEDIATE UNCHECKED
- SET INTEGRITY FOR <staging table name> IMMEDIATE CHECKED