Chapter 2. DB2 UDB’s materialized views 91
Figure 2-14 Multi-query optimization in REFRESH TABLE with materialized views
AST1 is a materialized view based on tables TRANS, STORE and CUST, while
AST2 is based on tables TRANS and STORE.
Consider issuing the following:
REFRESH TABLE AST1, AST2
This causes DB2 to attempt to match the materialized view queries to formulate a
“common subsumer” query CS, which is executed on the base tables, the results
of which are then suitably predicated to update AST1 and AST2 respectively.
This approach optimizes resource consumption against the base tables and
staging tables. This has a positive impact on the performance of SQL queries,
and updates of base tables associated with staging tables.
Considerations in grouping materialized views in a single REFRESH TABLE
statement include:
? Identical or overlapping base tables.
? Identical latency requirements for both materialized views, or at least
acceptable latency discrepancies between the materialized views.
? Large size of the base tables — significant performance gains can be
achieved in such cases.
INSERT INTO AST1
SELECT store_name, cust_name,
SUM(sales) AS ss, COUNT(*) AS cnt
FROM CS
WHERE year = 2001
GROUP BY store_name, cust_name
INSERT INTO AST2
SELECT store_name, year,
SUM(sales) AS ss, COUNT(*) AS cnt
FROM CS
WHERE year >= 1998
GROUP BY store_name, year
SELECT store_name, cust_name, year,
SUM(sales) AS ss, COUNT(*) AS cnt
FROM Trans T, Store S, Cust C
WHERE T.store_id = S.store_id
AND T.cust_id = C.cust_id
AND T.year >= 1998
GROUP BY store_name, year, cust_name
Common Subsumer CS
SELECT store_name, cust_name,
SUM(sales) AS ss, COUNT(*) AS cnt
FROM Trans T, Store S, Cust C
WHERE T.store_id = S.store_id
AND T.cust_id = C.cust_id
AND T.year = 2001
GROUP BY store_name, cust_name
SELECT store_name, year,
SUM(sales) AS ss, COUNT(*) AS cnt
FROM Trans T, Store S
WHERE T.store_id = S.store_id
AND T.year >= 1998
GROUP BY store_name, year
AST2
AST1
REFRESH TABLE AST1, AST2