Registering an existing table as a materialized query table

You can take advantage of automatic query rewrite for an existing table by registering it as a materialized query table.

Procedure

Begin general-use programming interface information. To register an existing table as a materialized query table:

Issue an ALTER TABLE statement and specify the ADD MATERIALIZED QUERY AS option. End general-use programming interface information.

Example

For example, assume that you have a very large transaction table named TRANS that contains one row for each transaction. The table has many columns, but you are interested in only the following columns:

ACCTID, which is the customer account ID
LOCID, which is the customer location ID
YEAR, which holds the year of the transaction

Begin general-use programming interface information.

You created another base table named TRANSCOUNT that consists of these columns and a count of the number of rows in TRANS that are grouped by the account, location, and year of the transaction. Suppose that you repopulate TRANSCOUNT periodically by deleting the rows and then by using the following INSERT statement:

INSERT INTO TRANSCOUNT (ACCTID, LOCID, YEAR, CNT)
   SELECT ACCTID, LOCID, YEAR, COUNT(*)
   FROM TRANS
   GROUP BY ACCTID, LOCID, YEAR;

You want to take advantage of automatic query rewrite for TRANSCOUNT by registering it as a materialized query table. You can do this by issuing the following ALTER TABLE statement:

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

This statement registers TRANSCOUNT with its associated subselect as a materialized query table, and DB2® can now use it in automatic query rewrite. The data in TRANSCOUNT remains the same, as specified by the DATA INITIALLY DEFERRED option.

You can still maintain the data, as specified by the MAINTAINED BY USER option, which means that you can continue to load, insert, update, or delete data. You can also use the REFRESH TABLE statement to populate the table. REFRESH DEFERRED indicates that the data in the table is the result of your most recent update or, if more recent, the result of a REFRESH TABLE statement.

The REFRESH TABLE statement deletes all the rows in a materialized query table, executes the fullselect in its definition, inserts the result into the table, and updates the catalog with the refresh timestamp and cardinality of the table. End general-use programming interface information.