88 High-Function Business Intelligence in e-business
on identifying these columns. Example 2-50 shows examples of columns
that form unique keys in different materialized views.
In the case of a partitioned materialized view, the partitioning key should
be a subset of the columns described above.
– Do
not create an index on the staging table, since such indexes will
degrade the performance of appends to the staging table.
– Create an informational or system enforced referential integrity (RI)
constraint on joins in a materialized view if appropriate, since DB2 takes
advantage of these constraints to optimize the maintenance of
materialized views.
Consider a materialized view with a join between the primary key of the
parent table and corresponding foreign key of the child table. DB2 takes
advantage of such an RI constraint to eliminate maintenance operations
on the materialized view. For example, DB2 deduces that an insert to the
parent table will not affect the materialized view since the join is empty.
That is, due to the RI constraint between the parent table and child table,
an insert of a row in the parent table guarantees that there can be no
matching rows in the child table.
It is more appropriate to create informational referential constraints to
achieve this optimization, rather than system enforced referential
constraints, since the latter has application development as well as
operations impact. Example 2-25 on page 54 shows an example of
system enforced and informational referential integrity constraints.
– Partition the staging table according to the partitioning of the materialized
view to promote collocated joins.
In choosing indexes, you should also take into account any joins necessitated
by REFRESH IMMEDIATE and staging materialized view maintenance
operations that must be included in packages updating the base tables. An
EXPLAIN of such packages will identify these maintenance operations which
might benefit greatly from appropriate indexes on the joined columns.
Example 2-50 Columns that form unique keys in materialized views
-- Case 1: A materialized view with a simple GROUP BY
--
CREATE TABLE loc_status_summary(locid, status, total, count) AS
(
SELECT t.locid, t.status, sum(ti.amount), COUNT(*)
FROM trans AS t, transitem AS ti
WHERE t.transid = ti.transid
Note: Unique indexes can not be defined on a materialized view.