Speed up MQT – Tips

by | Jul 11, 2008

Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2.

  1. Create an index on the GROUP BY columns. It helps speed up MQT look-ups needed to decide whether existing row needs to be changed, new one inserted, or last one deleted
  2. If not aggregating, create the index on the set of columns that form a unique key usually by concatenating the unique key columns of the individual base tables.
  3. If possible, define the base table columns used in the GROUP BY clause of REFRESH IMMEDIATE MQTs as NOT NULL makes finding matching rows in the MQT easier
  4. Define RI relationships wherever applicable allows various loss-less join specific optimizations (even not enforced)
  5. Always keep the base table and MQT statistics up-to-date and it helps the optimizer choose an optimal access plan