Creating a materialized query table

You can create a materialized query table, which is defined by the result of a query, to improve the performance of certain SQL applications.

About this task

Begin general-use programming interface information.

You should create materialized query tables in a table space that is defined as NOT LOGGED to avoid the performance overhead created by logging changes to the data.

Procedure

To create a new table as a materialized query table:

  1. Write a CREATE TABLE statement, and specify a fullselect.
    You can explicitly specify the column names of the materialized query table or allow Db2 to derive the column names from the fullselect. The column definitions of a materialized query table are the same as those for a declared global temporary table that is defined with the same fullselect.
  2. Include the DATA INITIALLY DEFERRED and REFRESH DEFERRED clauses when you define a materialized query table.
    DATA INITIALLY DEFERRED clause
    Db2 does not populate the materialized query table when you create the table. You must explicitly populate the materialized query table.
    • For system-maintained materialized query tables, populate the tables for the first time by using the REFRESH TABLE statement.
    • For user-maintained materialized query tables, populate the table by using the LOAD utility, INSERT statement, or REFRESH TABLE statement.
    REFRESH DEFERRED clause
    Db2 does not immediately update the data in the materialized query table when its base tables are updated. You can use the REFRESH TABLE statement at any time to update materialized query tables and maintain data currency with underlying base tables.
  3. Specify who maintains the materialized query table:
    MAINTAINED BY SYSTEM clause

    Specifies that the materialized query table is a system-maintained materialized query table. You cannot update a system-maintained materialized query table by using the LOAD utility or the INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements. You can update a system-maintained materialized query table only by using the REFRESH TABLE statement. BY SYSTEM is the default behavior if you do not specify a MAINTAINED BY clause.

    Create system-maintained materialized query tables in segmented or universal table spaces because the REFRESH TABLE statement triggers a mass delete operation.

    MAINTAINED BY USER clause
    Specifies that the table is a user-maintained materialized query table. You can update a user-maintained materialized query table by using the LOAD utility, the INSERT, UPDATE, MERGE, TRUNCATE, and DELETE statements, as well as the REFRESH TABLE statement.
  4. Specify whether query optimization is enabled.
    ENABLE QUERY OPTIMIZATION clause
    Specifies that Db2 can consider the materialized query table in automatic query rewrite. When you enable query optimization, Db2 is more restrictive of what you can select in the fullselect for a materialized query table.
    DISABLE QUERY OPTIMIZATION clause
    Specifies that Db2 cannot consider the materialized query table in automatic query rewrite.
    Recommendation: When creating a user-maintained materialized query table, initially disable query optimization. Otherwise, Db2 might automatically rewrite queries to use the empty materialized query table. After you populate the user-maintained materialized query table, you can alter the table to enable query optimization.

Results

The isolation level of the materialized table is the isolation level at which the CREATE TABLE statement is executed.

After you create a materialized query table, it looks and behaves like other tables in the database system, with a few exceptions. Db2 allows materialized query tables in database operations wherever it allows other tables, with a few restrictions. As with any other table, you can create indexes on the materialized query table; however, the indexes that you create must not be unique. Instead, Db2 uses the materialized query table's definition to determine if it can treat the index as a unique index for query optimization.

Example

The following CREATE TABLE statement defines a materialized query table named TRANSCNT. The TRANSCNT table 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.