One of the most powerful yet most often overlooked uses of Materialized Query Tables (MQTs) is to optimize access to frequently used subdomains of the data using a technique sometimes referred to as “Skinny MQTs” or speaking more academically akin to a domain index or a multi-table index. These MQTs do not contain summarized data, but rather help DB2 quickly boil done the qualifying rows without boiling the ocean, so to speak.
Sure, DB2 can efficiently scan independent indexes and do index ANDing and HASHing, or one could define MQTs for each and every query predicate (NOT a good idea), but often there is a very small set of common query subpredicates (meaning not the entire query predicate, but rather just snippets) and their qualifiers which occurs over and over again. More often than not, several hundred queries boil down to a dozen or less very common subpredicates.
Think about this style of MQT as a quick prequalification of the rows involved in a more complex query. There are common domains which are referenced over and over again, resulting from a combination of common JOINs and common subpredicates. For instance, in a reporting system there might be a dozen or more reports that use the domain of data representing “YESTERDAY”. One report might look at Sales yesterday overall, while another looks at it region by region, while another looks at it product by product. Each of these queries likely shares the common subpredicate “WHERE DATE = yesterday” which immediately limits the record set.
Using the summary approach to MQTs, you will end up with three separate MQTs to answer each of these queries, each of which has to be maintained, causing escalating overhead. If on the other hand, I construct an MQT which simply delimits the domain, i.e. “YESTERDAY”, and include the attributes which are not likely to change, such as “Region” and “Product_ID” and create an appropriate index over this single MQT, all queries about “YESTERDAY” can be satisfied. Back JOINs and row fetches are likely to be required, but for a far smaller subset of the data.
The idea here is simple: make it as fast and easy for DB2 to whack the data involved in the query down to size as quickly as possible, and without having to read several indexes, while at the same time avoiding the proliferation and subsequent overhead of many similar MQTs. And while the roots of MQTs may have been Automatic Summary Tables, sometimes their best use is to define the subdomains and attributes which help further refine those subdomains and an index over those columns.
By their very nature, “Fat” MQTs tend to answer a limited set of questions, whereas “Skinny” MQTs find far more reuse and also have less overhead and contention issues. Find the common patterns in your queries. Think about them in terms of the domains they express, and which attributes are used most often and which attributes will cause the size of the data to be reduced most quickly. These logical sets of data will also tend to have correlations with the physical clustering aspects of MDCs, so don’t be surprised if they look familiar. Find the common snippets in your predicates and implement MQTs and indexes on those MQTs to answer those queries.
