Creating materialized query tables

Materialized query tables improve the performance of complex queries that operate on very large amounts of data. Use the CREATE TABLE statement to create a materialized query table.

About this task

DB2® uses a materialized query table to precompute 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.

Procedure

To create a new materialized query table:

Issue the CREATE TABLE statement.

Example

Begin general-use programming interface information.

The following CREATE TABLE statement defines a materialized query table named TRANSCNT. TRANSCNT summarizes the number of transactions in table TRANS by account, location, and year.

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;

The fullselect, together with the DATA INITIALLY DEFERRED clause and the REFRESH DEFERRED clause, defines the table as a materialized query table.

End general-use programming interface information.