Materialized query tables and federated systems - overview

A materialized query table is a table that caches the results of a query. When you submit the query again, the database engine can return the data from the materialized query table instead of repeating the query computation.

You can use materialized query tables with nicknames to improve the performance of a query and to encapsulate a part of logic. Materialized query tables are used when you create cache tables.

The SQL optimizer determines if a query will run more efficiently with a materialized query table than the base tables or nicknames. The optimizer uses the following factors to select a materialized query table:
  • The materialized query table must match part or all of the query.
  • The refresh age criterion must be met.
  • The access plan that uses a materialized query table must be cheaper than the access plan that uses the base tables or nicknames.
Materialized query tables that involve nicknames for objects from the following data sources are supported:
  • Relational data sources
    • DRDA
    • Informix®
    • JDBC
    • ODBC
    • Oracle
    • Sybase
    • Microsoft SQL Server
    • Teradata
  • Non-relational data sources
    • Table-structured files