The Materialized Query Table

Materialized Query Tables (MQT) reside in tablespaces and are optimized in many of the same ways that tables. They can be indexed and should have RUNSTATS run against them. Particularly useful is the fact that MQTs are incorporated into the query plan of queries they can enhance without being explicitly called. This is advantageous in that a DBA can create an MQT expressly for the purpose of performance enhancement without the need for propagating its usage throughout the organization.

Like views MQTs can improve the performance of complex aggregates and crosstable joins. In addition an MQT can include any valid select construct including such things as UNION, INTERSECT, or EXCEPT. MQTs can also realize benneficial functionality with partitioned environments and in data warehousing/BI dimensioning.

A simple MQT can be created similiar to the following:

CREATE TABLE TableName AS
(
SELECT a.ThisCol, a.ThatCol, a.SomeCol, b.EvenCol, b.OddCol
FROM ThisTable a, ThatTable b
WHERE a.SomeCol = b.SameCol
AND a.ThisCol > 5
EXCEPT
SELECT c.ThisCol, c.ThatCol, c.SomeCol, d.EvenCol, d.OddCol
FROM ThisTable c, ThatTable d
WHERE c.SomeCol = d.SameCol
AND b.EvenCol IS NULL
)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE;

By specifying the refresh immediate I have made this a system managed MQT on which the data is refreshed as soon as changes are made. This preclude the usage of the MQT for updating underlying tables, but diminishes the personnel overhead associated with managing the database object.