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

42 High-Function Business Intelligence in e-business

򐂰 Have any constraints, unique indexes or triggers defined on it

򐂰 Be referenced in the definition of another materialized view

Also, you cannot ALTER a regular table into a staging table or vice versa.

2.6 Materialized view DROP considerations

When a materialized view is dropped, all dependencies are dropped and all

packages with dependencies on the materialized view are invalidated. Views

based on dropped materialized views are marked inoperative.

2.7 Materialized view matching considerations

The DB2 SQL Compiler analyzes user queries and produces an optimal access

path to produce the desired results. These are the two key components most

relevant to materialized views:

򐂰 Query rewrite component:

This component analyzes the query and if appropriate, rewrites this query into

another form that it believes will perform in superior fashion to the one written

by the user. This capability frees the user from having to deal with different

syntactic representations of the same semantic query, and instead focus on

using syntax (s)he is most comfortable with.

Part of this query rewrite process is the task of considering materialized views

for optimization. This includes checking for certain:

– States

– Matching criteria

򐂰 Cost based optimizer component

This component performs a cost analysis of materialized view processing

versus base table access, and decides on the optimal access path.

Figure 2-7 graphically depicts this process.

Chapter 2. DB2 UDB’s materialized views 43

Figure 2-7 Materialized view optimization flow

We focus here on the query rewrite component task relating to materialized

views, that is, state considerations and matching criteria considerations.

DB2’s Snapshot Monitor captures dynamic SQL statement executions in a

statement cache and gathers statistics about them, such as the number of

executions, number of rows reads and updated, and execution times. However,

this statement cache only includes the user’s original dynamic SQL query, and

not the version that gets to the DB2 optimizer after query rewrite.

Important: DB2’s EXPLAIN tables capture information about static and

dynamic SQL statements, costs, and access path selected for a query. The

EXPLAIN_STATEMENT table in particular, contains the text of the original

SQL statement entered by the user, along with the rewritten (if appropriate)

SQL statement used by the DB2 optimizer to choose the optimal access path

for executing the SQL query. This potentially modified SQL statement may

bear little resemblance to the original SQL query, as it may have been

rewritten and/or enhanced with additional predicates as determined by the

DB2 SQL Compiler.

CUST

TRANS

MQT

Query ... Against base tables CUST and TRANS

DB2 Query Rewrite

Rewrite ?

DB2 Optimizer

DB2 Optimizer

Materialized

view cheaper ?

Compensation

Yes

No

Yes

No