Paper Info
Title
Recommending Materialized Views and Indexes with IBM DB2 Design Advisor
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.
Year
Venue
Keywords
2004
ICAC
algorithm trade,real customer database,heuristic-based algorithm,present initial experiment,maintenance cost,database system,candidate MVs,Materialized Views,IBM DB2 Design Advisor,effective algorithm,computer algorithm,advanced tool
DocType
ISBN
Citations 
Conference
0-7695-2114-2
37
PageRank 
References 
Authors
1.96
0
8
Name
Order
Citations
PageRank
Daniel C. Zilio124715.41
Calisto Zuzarte226031.97
Guy M. Lohman32846965.94
Hamid Pirahesh436021109.95
Jarek Gryz597780.60
Eric Alton6371.96
Dongming Liang738621.64
Gary Valentin85310.59