The Double-Dipping Query Pattern and MQT Optimization in DB2

This article describes a general query pattern that helps keep aggregations to an efficient size. The pre-optimization (rewrite) step in DB2 is capable of rewriting a query using this pattern, so that it can use a materialized query table (MQT) even if some of the columns needed are not included in the MQT. This requires informational constraints to be defined, but allows us to keep the MQTs to an efficient size by excluding descriptive long columns and still be able to redirect queries that require such descriptive columns to these MQTs. Understanding the basic concepts behind the technique is helpful in designing aggregate tables. This is true even if we explicitly code queries to use MQTs, as well as when we rely on the pre-optimizer to match and redirect our queries to the appropriate MQTs.