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.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.
Procedure
To create a new materialized query table:
Example
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. 