Chapter 2. DB2 UDB’s materialized views 87
FACT1_SUBSET.AUTHOR_ID=AUTHOR.AUTHOR_ID AND MESH.ROOT_LEVEL='Anatomy'
AND MESH.FIRST_LEVEL IN ('Body Regions','Cells')
GROUP BY AUTHOR.AUTHOR_NAME, doc_id
SELECT Q3.$C2 AS "AUTHOR_NAME", Q3.$C1 AS "DOC_ID",
WHEN (Q3.$C1 = 1000)
ELSE NULL END
(SELECT SUM(Q2.$C2), Q2.$C0, Q2.$C1
(SELECT Q1.DOC_ID, Q1.AUTHOR_NAME, Q1.C
FROM DB2ADMIN.AST7 AS Q1
WHERE (Q1.ROOT_LEVEL = 'Anatomy') AND Q1.FIRST_LEVEL IN ('Body Regions',
'Cells')) AS Q2
GROUP BY Q2.$C1, Q2.$C0) AS Q3
2.9 Materialized view tuning considerations
Two broad categories of tuning considerations apply to materialized views as
User query related: These are the considerations related to improving the
performance of user queries against base tables that get routed to the
materialized view. This includes ensuring that RUNSTATS is current, and that
appropriate indexes exist on the materialized view.
Materialized view maintenance related: These are considerations related
to improving the performance of materialized view maintenance by DB2 when
updates occur on the underlying tables. We recommend that you follow these
– Create a non-unique index on the materialized view columns that
guarantee uniqueness of rows in a materialized view. Refer to “No
duplicate rows in materialized view restriction” on page 94 for guidelines
Attention: We did not conduct any performance measurements to determine
the performance gains of routing to the materialized views. In a real world
environment, you would need to continuously evaluate the efficacy of
materialized views, and create and drop them as needed based on
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.
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
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.