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

34 High-Function Business Intelligence in e-business

2.3.2 Immediate refresh

This maintenance approach is used when the materialized view must be kept in

sync with any changes in the base tables on which it has been defined are

updated. Such materialized views are called REFRESH IMMEDIATE tables.

Example 2-6 shows an example of SQL for creating a refresh immediate

materialized view.

Example 2-6 Creating a refresh immediate materialized view

CREATE SUMMARY TABLE dba.summary_sales

AS (SELECT ...........)

DATA INITIALLY DEFERRED

REFRESH IMMEDIATE

Tip: Incremental refresh should be used to reduce the duration of the refresh

process, and should be considered when one or more of the following

conditions exist:

򐂰 The volume of updates to the base tables relative to size of the base tables

is small.

򐂰 Duration of read only access to the base tables during a full refresh is

unacceptable.

򐂰 Duration of unavailability of the materialized view during a full refresh is

unacceptable.

Important: Not all materialized views can be defined to be REFRESH

IMMEDIATE. The principle behind what materialized view can be defined as

REFRESH IMMEDIATE is governed by the ability to compute the changes to

the materialized view from the delta changes to the base tables, and any other

base tables involved. Refer to 2.11, “Materialized view limitations” on page 92

for details about these restrictions.

Attention: Materialized view optimization occurs for both static and dynamic

SQL statements with REFRESH IMMEDIATE tables.

Note: The REFRESH TABLE statement can be issued against a REFRESH

IMMEDIATE materialized view — it is generally used for initially populating the

materialized view.

Chapter 2. DB2 UDB’s materialized views 35

REFRESH IMMEDIATE tables are synchronized with the base tables in the same

unit of work as the changes (inserts, updates or deletes) to the base tables.

Given the synchronous nature of the immediate refresh capability, the atomic

requirement for the change propagation can have a negative impact on

transactions updating the base tables.

An incremental update mechanism is used to synchronize a REFRESH

IMMEDIATE materialized view whenever update activity occurs against a base

table. The process involved is shown in Figure 2-5.

Figure 2-5 Immediate refresh using incremental update

When an SQL statement modifies a row in the base table, the following steps

occur in atomic fashion:

1. The modified row is captured.

2. The query defining the materialized view is computed based on the modified

row, computing the delta joins and delta aggregation to generate the data

necessary to update the materialized query table.

3. The delta is applied to the materialized view.

Note: This processing occurs at statement execution time as opposed to

occurring at commit time.

Base Table

T2

MV

T1,T2,..Tn

Base Table

Tn

Base Table

T1

Delta Select/Join

Delta

Aggregate

Delta Apply

SQL

INSERTs

UPDATEs

DELETEs

+

LOAD

Delta Propagation

in

same unit-of-work