Summary of MQT query recommendations

 

Follow these recommendations when using MQT queries.

• Do not include local selection or constants in the MQT because that limits the number of user specified queries that query optimizer can use the MQT in.

• For grouping MQTs, only use the SUM, COUNT, MIN, and MAX grouping functions. The query optimizer can recalculate AVG, STDDEV, and VAR_POP in user specified queries.

• Specifying FETCH FIRST N ROWS in the MQT limits the number of user specified queries that the query optimizer can use the MQT in and is not recommended.

• If the MQT is created with DATA INITIALLY DEFERRED, consider specifying the DISABLE QUERY OPTIMIZATION clause to prevent the query optimizer from using the MQT until it has been populated. When the MQT has been populated and is ready for use, the ALTER TABLE statement with the ENABLE QUERY OPTIMIZATION clause can used to enable the MQT for the query optimizer.

MQT tables need to be optimized just like non-MQT tables. Indexes should be created over the MQT columns that are used for selection, join and grouping as appropriate. Column statistics are collected for MQT tables.

The database monitor shows the list of MQTs considered during optimization. This information is in the 3030 record. If MQT usage has been enabled through the QAQQINI file and a MQT exists over at least one of the tables in the query, there will be a 3030 record for the query. Each MQT has a reason code indicating that it was used or if it was not used, why it was not used.

 

Parent topic:

Materialized query table optimization