Wednesday, April 13, 2005

Materialized Query Table support on DB2 UDB for iSeries

Materialized Query Table support on DB2 UDB for iSeries: "Materialized Query Tables"
With the release V5R3M0, DB2 Universal Database(TM) for iSeries(TM) announced support for creating and maintaining Materialized Query Tables (MQTs) (also referred to as automatic summary tables or materialized views). On April 29, 2005, query optimizer support will be available which will allow the optimizer to implicitly recognize MQTs and use them when optimizing and running queries, with the goal of improved performance.

Materialized Query Tables are created using the SQL CREATE TABLE statement. Alternatively, the ALTER TABLE statement may be used to convert an existing table into a materialized query table. The REFRESH TABLE statement is used to recompute the results stored in the MQT. For user-maintained MQTs, the MQTs may also be maintained by the user via INSERT, UPDATE, and DELETE statements.

The MQT contains the precomputed results of a query in the materialized query table. The database engine can use these precomputed results instead of computing them for a user specified query. The query optimizer will analyze any applicable MQTs and can choose to implement the query using a given MQT, providing a faster implementation than accessing the base tables.

Additional information will be available on this web site on April 29, 2005 including a list of PTFs needed for the support and where to find additional documentation on using MQTs.

No comments:

Post a Comment