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

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

Optimized Statement:

-------------------

SELECT Q3.$C2 AS "AUTHOR_NAME", Q3.$C1 AS "DOC_ID",

CASE

WHEN (Q3.$C1 = 1000)

THEN Q3.$C0

ELSE NULL END

FROM

(SELECT SUM(Q2.$C2), Q2.$C0, Q2.$C1

FROM

(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

follows:

򐂰

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

guidelines:

– 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

performance needs.

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.

– Do

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

materialized views.

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.