The Community for Technology Leaders
Autonomic Computing, International Conference on (2004)
New York, New York
May 17, 2004 to May 18, 2004
ISBN: 0-7695-2114-2
pp: 180-188
Eric Alton , York University
Wenbin Ma , IBM Canada
Sam Lightstone , IBM Canada
Hamid Pirahesh , IBM Almaden Research Center
Guy M. Lohman , IBM Almaden Research Center
Roberta J. Cochrane , IBM Almaden Research Center
Gary Valentin , IBM Haifa Research Lab
Dongming Liang , York University
Calisto Zuzarte , IBM Canada
Jarek Gryz , York University
Latha Colby , IBM Almaden Research Center
Daniel C. Zilio , IBM Canada
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.
Eric Alton, Wenbin Ma, Sam Lightstone, Hamid Pirahesh, Guy M. Lohman, Roberta J. Cochrane, Gary Valentin, Dongming Liang, Calisto Zuzarte, Jarek Gryz, Latha Colby, Daniel C. Zilio, "Recommending Materialized Views and Indexes with IBM DB2 Design Advisor", Autonomic Computing, International Conference on, vol. 00, no. , pp. 180-188, 2004, doi:10.1109/ICAC.2004.47
106 ms
(Ver 3.1 (10032016))