Recommending materialized views and indexes with the IBM DB2 design advisor

TLDR

Materialized views and indexes accelerate query processing but require storage and maintenance, and selecting the optimal set is complex due to workload, database, and other factors, making heuristic approaches often impractical. The paper introduces a tool that leverages the query optimizer to suggest and evaluate materialized views and indexes, along with a practical algorithm for quickly finding effective solutions for large workloads. The algorithm balances update and storage costs against query benefits, while the tool automatically gathers workload and system data, optionally samples candidate views, and uses multi‑query optimization to build cost‑effective materialized views that benefit many queries. Initial experiments on a real customer database confirm that the system produces high‑quality materialized view and index recommendations.

Abstract

Materialized views (MVs) and indexes both significantly speed query processing in database systems, but consume disk space and need to be maintained when updates occur. Choosing the best set of MVs and indexes to create depends upon the workload, the database, and many other factors, which makes the decision intractable for humans and computationally challenging for computer algorithms. Even heuristic-based algorithms can be impractical in real systems. In this paper, we present an advanced tool that uses the query optimizer itself to both suggest and evaluate candidate MVs and indexes, and a simple, practical, and effective algorithm for rapidly finding good solutions even for large workloads. The algorithm trades off the cost for updates and storing each MV or index against its benefit to queries in the workload. The tool autonomically captures the workload, database, and system information, optionally permits sampling of candidate MVs to better estimate their size, and exploits multi-query optimization to construct candidate MVs that will benefit many queries, over which their maintenance cost can then be amortized cost-effectively. We describe the design of the system and present initial experiments that confirm the quality of its results on a database and workload drawn from a real customer database.