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

Chapter 2. DB2 UDB’s materialized views 45

2.7.3 Matching permitted

Materialized views will be considered for optimization in the following cases:

1. Superset predicates and perfect match:

This is the simplest case where the user query has the same number of

tables as in the materialized view, and the same expressions, and requests

an answer that can be fully met with the data in the materialized view. Here,

the predicates involved in the materialized view must be a superset of those

involved in the query. In DB2 V7, predicate analysis to detect this was limited,

with only exact matches, or simple equality predicates and IN predicates

being considered. In DB2 V8, the analysis has been expanded to cover a

broader range of predicates.

Scenario 1: Consider the materialized view shown in Example 2-7:

Example 2-7 Superset predicates and perfect match materialized view 1

CREATE SUMMARY TABLE custtrans AS

(

SELECT cust_id, COUNT(*) AS counttrans

FROM trans

GROUP BY cust_id

)

DATA INTITIALLY DEFERRED REFRESH DEFERRED

A query that looks like the one shown in Example 2-8 will be considered

matching for materialized view optimization purposes.

Example 2-8 Superset predicates and perfect match — matching query 1

SELECT cust_id, COUNT(*)

FROM trans

WHERE cust_id > 1000

GROUP BY cust_id

Scenario 2: Consider the materialized view shown in Example 2-9:

Example 2-9 Superset predicates and perfect match materialized view 2

CREATE SUMMARY TABLE custtrans AS

(

SELECT cust_id, COUNT(*) AS counttrans

FROM trans

WHERE cust_id > 500

GROUP BY cust_id

)

DATA INTITIALLY DEFERRED REFRESH DEFERRED

46 High-Function Business Intelligence in e-business

A query that looks like the one shown in Example 2-10 will be considered

matching for materialized view optimization purposes.

Example 2-10 Superset predicates and perfect match — matching query 2

SELECT cust_id, COUNT(*)

FROM trans

WHERE cust_id > 1000

GROUP BY cust_id

Figure 2-8 provides additional examples of matching conditions. The “Valid ?”

column indicates whether or not the materialized view will be considered for

optimization or not for the given query.

Figure 2-8 Matching columns, predicates, and expressions

2. Aggregation functions and grouping columns:

Aggregation collapses related groups of rows, resulting in a smaller size of

the materialized view. It is not necessary to define different materialized views

for each type of user grouping. Under certain conditions, DB2 can decide to

use a materialized view even if the materialized view’s grouping is different

from that of the user query. For instance, if the materialized view has a

GROUP BY on a finer granularity, DB2 can compute the result of a coarser

granularity GROUP BY by doing further aggregation

12

on top of the

materialized view as shown in the following discussion.

QUERY Materialized View Valid ?

... cust_age >= 15 ... ... cust_age >= 20 ...

... cust_age >= 25 ... ... cust_age >= 20 ...

... trans_yr IN (2001, 2002) ... ... trans_yr IN (2000, 2001) ...

... trans_yr = 2002 ... ... trans_yr IN (2000, 2001, 2002) ...

SELECT date, cust_id, SUM(sales) FROM

TRAN WHERE ...

SELECT cust_id, SUM(SALES) FROM

TRAN WHERE ...

SELECT cust_id, INT((cust_age + 5) / 10),

SUM(sales) FROM ... WHERE ...

SELECT cust_id, INT(cust_age / 10),

SUM(sales) FROM ... WHERE ...

No

No

No

Yes

No

Yes

12

This is also called compensation.

Chapter 2. DB2 UDB’s materialized views 47

Scenario 1:

Consider the materialized view shown in Example 2-11, which

has one row for every month of every year.

Example 2-11 Aggregation functions & grouping columns materialized view 1

CREATE SUMMARY TABLE dba.trans_agg AS

(

SELECT ti.pgid, t.locid, t.acctid, t.status,

YEAR(pdate) as year, MONTH(pdate) AS month,

SUM(ti.amount) AS amount, COUNT(*) AS count

FROM transitem AS ti, trans AS t

WHERE ti.transid = t.transid

GROUP BY YEAR(pdate), MONTH(pdate)

)

DATA INITIALLY DEFERRED REFRESH IMMEDIATE

The query shown in Example 2-12, with a GROUP BY on YEAR, can be

computed from the above materialized view by aggregating all the months of

a year.

Example 2-12 Aggregation functions & grouping columns — matching query 1

SELECT ti.pgid, t.locid, t.acctid, t.status,

YEAR(pdate) AS year, MONTH(pdate) AS month,

SUM(ti.amount) AS amount, COUNT(*) AS count

FROM transitem AS ti, trans AS t

WHERE ti.transid = t.transid

GROUP BY YEAR(pdate)

This capability allows the DBA to optimize by only defining one materialized

view at the month level. This is the simplest form of matching handled by DB2

as far as grouping columns are concerned. The number of materialized views

can be minimized by using complex constructs that include grouping sets,

ROLLUP and CUBE operators. Refer to 3.3.2, “GROUPING capabilities

ROLLUP & CUBE” on page 125 for an overview of DB2’s support of complex

GROUP BY constructs.

Following are some scenarios using grouping sets:

Scenario 2: Consider the materialized view shown in Example 2-13.

Example 2-13 Aggregation functions & grouping columns materialized view 2

CREATE SUMMARY TABLE AST1 AS

(

SELECT .....GROUP BY GROUPING SETS

((customer_id, product_group_id), YEAR(date_col), MONTH(date_col))

)