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

Chapter 2. DB2 UDB’s materialized views 65

The decision to use filtering predicates should be based on domain expertise,

and the frequency of query requests.

More detailed examples of generalizing local predicates are described in

“Generalizing local predicates application example” on page 69.

Example 2-36 Generalize simple predicate to GROUP BY in a materialized view

CREATE SUMMARY TABLE custtrans AS

(

SELECT cust_id, cust_age, COUNT(*) AS counttrans

FROM trans

GROUP BY cust_id, cust_age

)

DATA INTITIALLY DEFERRED REFRESH DEFERRED

2.8.3 Step 3: Create the materialized view

This step involves determining whether the query under consideration can reuse

an existing materialized view “as is” or with some modifications, or require the

creation of a new materialize view altogether.

In case an existing materialized view can be reused without modifications, then

we need to proceed to bypass size estimation, and check whether the query

under consideration actually routes to the materialized view. This is discussed in

Step 5: Verify query routes to “empty” the materialized view.

2.8.4 Step 4: Estimate materialized view size

A key considerations in the design of materialized views is its size. Typically, the

materialized view should be less than an order of magnitude in size as compared

with the data in the base table(s). Large materialized views impact the cost

formulas for access which might result in their being ignored for routing even

though matching criteria are met. Large materialized views occupy more disk

space, and potentially increase refresh cycle duration, thereby reducing

availability of the materialized view.

If an estimate of the materialized view exceed this order of magnitude

threshold

13

, then this materialized view needs to be reduced in size by splitting it

into two or more materialized views, and/or by adding filtering predicates to

reduce the number of rows.

13

Order of magnitude is a rule-of-thumb number — you can choose to revise this number upward or

downward based on personal experience.