Decision support queries are often difficult and expensive. They typically operate over a large amount of data and may have to scan or process terabytes of data and possibly perform multiple joins and complex aggregations. With these types of queries, traditional optimization and performance is not always optimal.
In DB2 V8 and later, one solution is the use of Materialized Query Tables (MQTs). This practice lets you precompute whole or parts of each query and then use computed results to answer future queries. MQTs provide the means to save the results of previous queries and then reuse the common query results in subsequent queries. This practice helps avoid redundant scanning, aggregating, and joins. MQTs are also useful for data warehouse type applications.
MQTs do not completely eliminate optimization problems, but rather move optimization issues to other areas. Some challenges include finding the best MQT for an expected workload, maintaining the MQTs when underlying tables are updated, ability to recognize usefulness of MQT for a query, and the ability to determine when DB2 will actually use the MQT for a query. Most of these types of problems are addressed by OLAP tools, but MQTs are the first step.
The main advantage of the MQT is that DB2 can recognize a summary query against the source tables for the MQT, and can rewrite the query to use the MQT instead. It is, however, your responsibility to move data into the MQT by using a REFRESH TABLE command, or by manually moving the data yourself.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|