DB2® Version 8: The Official Guide

Leveraging Materialized Query Tables for Improved Query Performance

For the query optimizer to consider a user-maintained MQT in determining an access path, the following conditions must be met:

• The MQT must have ENABLE QUERY OPTIMIZATION set (this is done in the CREATE TABLE or ALTER TABLE statement).

• You must set the following special registers:

  								DB2 SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION USER
  								DB2 SET CURRENT REFRESH AGE ANY
  							

• The MQT cannot be in a CHECK PENDING NO ACCESS state.

• The query optimization level must be set at Level 2 or at a level greater than or equal to 5:

  								DB2 SET CURRENT QUERY OPTIMIZATION LEVEL 2
  							

After creating and populating a user-maintained MQT and setting the appropriate registers, update the query optimization ...