Monday, October 17, 2011

WOW for MDC on MQT - How to speed up the load process for a warehouse

When I was teaching a performance bootcamp earlier this month, one of the topics was about combining multi-dimensional clustering (MDC) with range-partitioned tables. Then a question came up about using MDC with materialized query tables (MQT) and whether it was possible to combine the two techniques. The customer hadn't succeeded before. As I didn't see a reason for why not combining the two I looked for documentation and examples: The DB2 Information Center describes how to combine MDC with MQT.

With the MQTs some of the heavy queries can be sped up by precomputing the answers for common complex subqueries. Using MDC for the MQTs can improve performance even further, depending on what can be clustered and how many dimensions are present.

I also briefly tested db2look and how it reproduces the DDL for it and the combination is supported by the tool, too.

For the customer it was a "Wow!" for the MDC on MQT, for the users it will mean faster queries...