Materialized query table optimization

 

Materialized query tables (MQTs) (also referred to as automatic summary tables or materialized views) can provide performance enhancements for queries.

This is done by precomputing and storing results of a query in the materialized query table. The database engine can use these results instead of recomputing them for a user specified query. The query optimizer will look for any applicable MQTs and can choose to implement the query using a given MQT provided this is a faster implementation choice.

Materialized Query Tables are created using the SQL CREATE TABLE statement. Alternatively, the ALTER TABLE statement may be used to convert an existing table into a materialized query table. The REFRESH TABLE statement is used to recompute the results stored in the MQT. For user-maintained MQTs, the MQTs may also be maintained by the user via INSERT, UPDATE, and DELETE statements.

• MQT supported function
  Although a MQT can contain almost any query, the optimizer only supports a limited set of query functions when matching MQTs to user specified queries. The user specified query and the MQT query must both be supported by the SQE optimizer.

• Using MQTs during Query optimization
  Before using MQTs, consider your environment attributes.

• MQT examples
  The following are examples of using MQTs.

• Details on the MQT matching algorithm
  What follows is a generalized discussion of how the MQT matching algorithm works.

• Determining unnecessary MQTs
  You can easily determine which MQTs are being used for query optimization. However, you can now easily find all MQTs and retrieve statistics on MQT usage as a result of iSeries™ Navigator and i5/OS™ functionality.

• Summary of MQT query recommendations
  Follow these recommendations when using MQT queries.

 

Parent topic:

Processing queries: Overview

 

Related information

Create Table statement