DB2® Version 8: The Official Guide

Incremental Maintenance of Materialized Query Tables

An MQT is a table with a definition based on the result of a query (a fullselect). Such a table is populated with precomputed results taken from one or more tables on which the MQT definition is based. A summary table is a type of MQT with a fullselect that contains a GROUP BY clause that summarizes data from the tables that are referenced in the fullselect. A summary table is a descendent of a parent table.

The following statement creates a summary table named AVG_SAL, which has two columns, JOB and AVG_SALARY. The AVG_SALARY column is computed from the SALARY column of the parent table, STAFF:

						db2 create table avg_sal as
						   (select job, avg(salary) as avg_salary
						      from staff group by job)