MQTs can contain pre computed and/or subsets of data.

MQTs can have indexes; the 

RUNSTATS

 command can be performed against 

them.

MQTs can use multi dimensionally clustered and regular tables as source 

tables.

MQTs can be refreshed incremental.

MQTs are automatically used by the optimizer if applicable.

The following MQT enhancements can result in improved query performance:

Query routing enhancements

Queries can now be routed to MQTs whose definitions contain a join that is 

not aggregated. Prior to Version 8, an MQT definition could only reference a 

join that was aggregated. For example, in Version 8 the table described in 

Example 10 9, which contains a join, can be created to store the customer 

and account information for bad accounts.

Example 10 9   Sample MQT

CREATE TABLE bad_account AS (

SELECT customer_name, customer_id, a.balance

FROM account a, customers c

WHERE

status IN ( delinquent ,  problematic ,  hot )

AND a.customer_id = c.customer_id)

DATA INITIALLY DEFERRED REFRESH DEFERRED

If a user asks whether an account is delinquent, the DB2 UDB optimizer 

recognizes that the MQT has cached the requested information, and instead 

of accessing the base table 

ACCOUNT

, DB2 accesses the MQT named 

BAD_ACCOUNT

, which provides a better response time and can be used to return 

customer information.

User maintained materialized query tables

Many custom applications maintain and load tables that are really 

precomputed data representing the result of a query. By identifying a table as 

a user maintained materialized query table, dynamic query performance can 

be improved. Such MQTs are maintained by users rather than by the system. 

UPDATE

, 

INSERT

, and 

DELETE

 operations are permitted against user maintained 

MQTs.

Setting appropriate special registers allows the query optimizer to take 

advantage of the precomputed query result that is already contained in the 

user maintained MQT.

Materialized query tables on nicknames

 Chapter 10. Advanced DB2 UDB features 

329