Dynamic materialization of query views for data warehouse workloads

Abstract

A materialized view, or Materialized Query Table (MQT), Is an auxiliary table with precomputed data that can be used to significantly improve the performance of a database query. Previous research efforts have focused on finding the best candidate MQT set, with a common static heuristic being to greedily pre-materialize the MQTs prior to executing the workload. While this approach Is sound when the size of the MQT set on disk Is small, it will not be able to pre-materialize all MQTs and Indexes when faced with real-world disk limits and view maintenance costs, and thus a static heuristic will fall to exploit the potentially large benefits of those MQTs not selected for materialization. In this paper we present an automated, dynamic MQT management scheme that materializes views and creates Indexes in an on-demand fashion as a workload executes and manages them with an LRU cache. In order to maximize the benefit of executing queries with MQTs, the scheme makes an adaptive tradeoff between the MQT materializations, the base table accesses, and the benefit of MQT hits in the cache. To find the workload permutation that produces the overall highest net benefit, we use a genetic algorithm to search the N! solution space, and to avoid materializing seldom-used MQTs, we prune the set of MQT candidates. We ran our dynamic management on a TPC-H workload and found that our scheme produces higher benefit across a variety of scenarios; we demonstrate over 60% improvement in MQT benefit under harsh conditions when the size of available MQTs with indexes Is larger than the cache. © 2008 IEEE.