Creation of materialized query tables

Materialized query tables improve the performance of complex queries that operate on large amounts of data.

Using a materialized query table, DB2® pre-computes the results of data that is derived from one or more tables. When you submit a query, DB2 can use the results that are stored in a materialized query table rather than compute the results from the underlying source tables on which the materialized query table is defined. If the rewritten query is less costly, DB2 chooses to optimize the query by using the rewritten query, a process called automatic query rewrite.

To take advantage of automatic query rewrite, you must define, populate, and periodically refresh the materialized query table. You use the CREATE TABLE statement to create a table as a materialized query table.

CREATE TABLE TRANSCNT (ACCTID, LOCID, YEAR, CNT) AS
  (SELECT ACCOUNTID, LOCATIONID, YEAR, COUNT(*)
     FROM TRANS
     GROUP BY ACCOUNTID, LOCATIONID, YEAR )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED 
     MAINTAINED BY SYSTEM
     ENABLE QUERY OPTIMIZATION;