Chapter 2. DB2 UDB’s materialized views 95
Certain operations cannot be performed on the base tables of a materialized
view that needs to be incrementally maintained.
? IMPORT REPLACE cannot be used on an base table of a materialized view.
? ALTER TABLE NOT LOGGED INITIALLY WITH EMPTY TABLE cannot be
done on a base table of a materialized view.
? Materialized views cannot be used as exception tables to collect information
when constraints are being validated during bulk constraints checking (during
LOAD or executing the SET INTEGRITY statement).
2.12 Replicated tables in nodegroups
In a partitioned database, the performance of join queries can be greatly
enhanced through collocation of rows of the different tables involved in the join.
Figure 2-16 describes such an environment, where the STORE and TRANS
tables have been partitioned on
storeid column. An SQL query that requires a
join on the
storeid column will see significant performance benefits from this
partitioning scheme, because of the greater parallelism achieved through
collocated joins.
However, when the CUST table is also involved in the join, then a collocated join
is not possible, since the CUST table does not have a
storeid column, and
therefore cannot be partitioned by
storeid. While DB2 UDB can choose to
perform a directed join in this particular case
14
, the performance of such joins is
less efficient than that of a collocated join, since the movement of rows is inline
with query execution.
Note: DB2 sometimes allows you to define a REFRESH DEFERRED table
even though it cannot use it for materialized view optimization. In such cases,
it issues a warning SQL20059W (sqlstate 01633).
A typical scenario for this functionality is when a database administrator needs
to:
1. Create a data mart based on detailed data from operational systems.
2. Provide direct access to end users to this data mart only,
without allowing
them access to the base detailed data.
3. Control the refresh cycle of this data mart via the REFRESH TABLE
statement.
This is an example of creating an materialized view that is directly accessible
by end users, and is not involved in materialized view optimization.