A materialized query table (MQT) is basically a pre-calculated and stored view. While an MQT can be used for any kind of view, it would more commonly be used for aggregation of data, or any kind of query that involves a fair amount of calculations to create a result set. Since an MQT is stored as a physical table, it can have indexes defined on it for efficient access.
The main advantage of an MQT is that if an MQT is defined as a particular query, the DB2 optimizer is intelligent enough to recognize a subsequent query that comes along with a similar or identical definition, and rather than process the new query to calculate the result set, it can just “reroute” the query to use the previously calculated and stored results in the MQT table. The end user or program that is executing the query does not even need to be aware of the existence of the MQT . . . this reroute is performed automatically by the DB2 optimizer. However, the MQT can also be accessed directly via SQL statements if the user or program wishes to do so.
The automatic reroute of queries to be able to access the MQT can be disabled if the MQT is defined with the “DISABLE QUERY OPTIMIZATION” option. In that case, the MQT can only be accessed directly via SQL.
One of the disadvantages of MQTs is that the data that is stored in them can and will become stale if the source tables from which the MQT data was originally derived from are updated. The MQT can be refreshed periodically via an explicitly invoked “REFRESH TABLE” SQL statement, which will re-execute the query that defines the MQT and store the new result set, overlaying the previous results.
There are two “flavors” of MQTs: “maintained by system”, or “maintained by user”. System maintained MQTs can only be populated or refreshed by a “REFRESH TABLE” statement. User maintained MQT’s can be populated or refreshed either by a “REFRESH TABLE” statement, by a LOAD utility, or by SQL DML (INSERT, UPDATE or DELETE) statements.