DB2 UDB's High-Function Business Intelligence in e-business

90 High-Function Business Intelligence in e-business

2.10 Refresh optimization

REFRESH TABLE operations can have the following negative impacts:

򐂰 Refresh takes a z-lock on the materialized view thus making it unavailable for

access by SQL queries. Performance can be significantly impacted for

queries depending on materialized view optimization during the refresh

window.

򐂰 Refresh also takes a z-lock on the staging table (if one exists). This can have

a negative impact on updates to the base tables (they will not succeed), if

refresh takes an extended period of time, since the staging table is updated in

the same unit-of-work as updates to the base table.

򐂰 Refresh causes logging to occur as a consequence of updates to the

materialized view, as well as pruning of the staging table. Refresh also

consumes CPU, IO and buffer pool resources that impacts other users

contending for the same resources.

Refresh resource consumption can be reduced by combining multiple

materialized view refreshes in a single REFRESH TABLE statement. DB2 uses

“multi-query optimization” to share joins and aggregations required of each

materialized view in order to reduce the resource consumption against base

tables shared by the materialized views. Figure 2-14 describes this process.

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