Materialized query table node

A materialized query table node represents a materialized query table that is referenced in the SQL statement. A materialized query table stores the results of common subqueries and is used to speed up complex online analytical processing (OLAP) queries.

DB2® for z/OS® supports two types of materialized query tables: a system-maintained materialized query table and a user-maintained materialized query table. You can insert, update, and delete records in a user-maintained materialized query table; you cannot insert, update, or delete records in a system-maintained materialized query table.

A materialized query table node is labeled with the name of the materialized query table , and the default shape for this type of node is an upside-down trapezoid. The table's correlation name, creator name, or cardinality can also be displayed on the label. If the RUNSTATS utility has not collected statistics for the table, the node is outlined in red. If the optimizer uses the default value for the cardinality, the cardinality is marked as the default.

How DB2 accesses a materialized query table

DB2 accesses a materialized query table the same way as it accesses a base table with one exception: DB2 cannot use a direct fetch to access an materialized query table.

When underlying data changes, DB2 does not automatically refresh materialized query tables. To keep the materialized query table data current, you must issue a REFRESH TABLE statement if the table is system-maintained. If the materialized query table is user-maintained, you must issue INSERT, UPDATE, and DELETE statements as needed. To control the use of materialized query tables, use the CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special registers.


Feedback