DB2 10.5 for Linux, UNIX, and Windows

User-maintained materialized query tables

User-maintained materialized query tables (MQTs) are a powerful way to improve response time for complex analytical queries because their data consists of precomputed results from the tables that you specify in the materialized query table definitions.

MQTs can help improve response time particularly for queries that use one or more of the following types of data:

Aggregate data over one or more dimensions
Joins and aggregate data over a group of tables
Data from a commonly accessed subset of data
Repartitioned data from a table, or part of a table, in a partitioned database environment

The larger the base tables, the more significant are the potential improvements in response time when you use MQTs.

Knowledge of MQTs is integrated into the SQL and XQuery compiler. During the query rewrite phase, the optimizer determines whether to use an available MQT in place of accessing the referenced base tables directly. If an MQT is used, you need access privileges on the base tables, not the MQT, and the explain facility can provide information about which MQT was selected.

MQTs can effectively eliminate overlapping work among queries. Computations are performed only once when MQTs are built and once each time that they are refreshed, and their content can be reused during the execution of many queries.

Because MQTs behave like regular tables in many ways, use the same guidelines for optimizing data access, such as using table space definitions and indexes and running the RUNSTATS utility.

DB2® Cancun Release 10.5.0.4 and later includes support for column-organized user-maintained MQTs. If you are upgrading your DB2 server from version 10.1 or earlier releases and you plan to convert row-organized tables with existing MQTs to column-organized tables, using column-organized user-maintained MQTs can simplify portability and improve query performance.