Creating and altering a materialized query table

If the optimizer determines that a query runs faster against a materialized query table than it does against the base table or tables, the query will run against the materialized query table. You can directly query a materialized query table. For more information about how the optimizer uses materialized query tables, see the Database performance and query optimization topic.

Assume a very large transaction table named TRANS contains one row for each transaction processed by a company. The table is defined with many columns. Create a materialized query table for the TRANS table that contains daily summary data for the date and amount of a transaction by issuing the following:

CREATE TABLE STRANS
  AS (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM
      FROM TRANS 
      GROUP BY YEAR, MONTH, DAY )
  DATA INITIALLY DEFERRED
  REFRESH DEFERRED
  MAINTAINED BY USER

This materialized query table specifies that the table is not populated at the time that it is created by using the DATA INITIALLY DEFERRED clause. REFRESH DEFERRED indicates that changes made to TRANS are not reflected in STRANS. Additionally, this table is maintained by the user, enabling the user to use ALTER, INSERT, DELETE, and UPDATE statements.

To populate the materialized query table or refresh the table after it has been populated, use the REFRESH TABLE statement. This causes the query associated with the materialized query table to be run and causes the table to be filled with the results of the query. To populate the STRANS table, run the following statement:

REFRESH TABLE STRANS

You can create a materialized query table from an existing base table as long as the result of the select-statement provides a set of columns that match the columns in the existing table (same number of columns and compatible column definitions). For example, create a table TRANSCOUNT. Then, change the base table TRANSCOUNT into a materialized query table:

To create the table:

CREATE TABLE TRANSCOUNT
    (ACCTID SMALLINT NOT NULL,
     LOCID SMALLINT,
     YEAR DATE
     CNT INTEGER)

You can alter this table to be a materialized query table:

ALTER TABLE TRANSCOUNT
  ADD MATERIALIZED QUERY
      (SELECT  ACCTID, LOCID, YEAR, COUNT(*) AS CNT
      FROM TRANS 
      GROUP BY ACCTID, LOCID, YEAR )
  DATA INITIALLY DEFERRED
  REFRESH DEFERRED
  MAINTAINED BY USER

Finally, you can change a materialized query table back to a base table. For example:

ALTER TABLE TRANSCOUNT
      DROP MATERIALIZED QUERY

In this example, the table TRANSCOUNT is not dropped, but it is no longer a materialized query table.