Using materialized query tables to improve SQL performance

Materialized query tables can simplify query processing, greatly improve the performance of dynamic SQL queries, and are particularly effective in data warehousing applications, where you can use them to avoid costly aggregations and joins against large fact tables.

About this task

Begin program-specific programming interface information. Materialized query tables are tables that contain information that is derived and summarized from other tables. Materialized query tables pre-calculate and store the results of queries with expensive join and aggregation operations. By providing this summary information, materialized query tables can simplify query processing and greatly improve the performance of dynamic SQL queries. Materialized query tables are particularly effective in data warehousing applications.

Automatic query rewrite is the process Db2 uses to access data in a materialized query table. If you enable automatic query rewrite, Db2 determines if it can resolve a dynamic query or part of the query by using a materialized query table.

Procedure

To take advantage of eligible materialized query tables:

Rewrite the queries to use materialized query tables instead of the underlying base tables.
Keep in mind that a materialized query table can yield query results that are not current if the base tables change after the materialized query table is updated. End program-specific programming interface information.