Jeff Magnusson
ABSTRACT
For queries to be efficiently processed in a database, the query optimizer must be presented with an efficient set of indexes for the query workload. Due to the dynamic and complex nature of modern database workloads, database administrators and developers often rely on autonomic tuning tools to determine an optimal set of indexes for the workload. These autonomic tuning tools rely on the use of virtual, or "what-if", indexes. Virtual indexes are limited in the respect that they only provide tuning recommendations given the sizes and distribution of data currently housed in the database. In an active database system, relations and the distribution of data within them are constantly changing at different relative rates. The optimal set of indexes for completing a query will change when related data distributions grow relative to one another past some data threshold. In this paper, we propose a novel extension to the virtual index concept that allows estimating query behavior and index utilization at future points in time. Thus, data thresholds can be proactively detected and the future viability of a given index set can be assessed. The implementation and various applications of this new technology are discussed.
- "Database Cost of Ownership Study," The Aberdeen Group 1998.Google Scholar
- "DB2 UDB vs. Oracle8i: Total Cost of Ownership," D. H. Brown Associates, Inc, Port Chester, NY 2000.Google Scholar
- S. Agrawal, S. Chaudhuri, L. Kollar, A. Marathe, V. Narasayya, and M. Syamala, "Database Tuning Advisor for Microsoft SQL Server 2005," in Proceedings of the 30th VLDB Conference, Toronto, Canada, 2004, pp. 1110--1121.Google Scholar
- N. Bruno and S. Chaudhuri, "Automatic Physical Database Tuning: A Relaxation-Based Approach," in SIGMOD 2005, Baltimore, Maryland, USA, 2005, pp. 227--238. Google ScholarDigital Library
- N. Bruno and S. Chaudhuri, "Online AutoAdmin: (Physical Design Tuning)," in SIGMOD 2007, Beijing, China, 2007, pp. 1067--1069. Google ScholarDigital Library
- S. Chaudhuri and N. Vivek, "AutoAdmin "what-if" index analysis utility," in SIGMOD 1998, Seattle, Washington, USA, 1998, pp. 367--378. Google ScholarDigital Library
- B. Dageville, D. Das, K. Dias, K. Yagoub, M. Zait, and M. Ziauddin, "Automatic SQL Tuning in Oracle 10g," in Proceedings of the 13th VLDB Conference Toronto, Canada, 2004, pp. 1098--1109. Google ScholarDigital Library
- S. Lifschitz and M. A. Vaz Salles, "Autonomic Index Management," in Proceedings of the Second International Conference on Autonomic Computing, Seattle, Washington, USA, 2005, pp. 304--305. Google ScholarDigital Library
- K. U. Sattler, I. Geist, and E. Schallehn, "QUIET: Continuous Query-driven Index Tuning," in Proceedings of the 29th VLDB Conference, Berlin, Germany, 2003, pp. 1129--1132. Google ScholarDigital Library
- D. C. Zilio, J. Rao, S. S. Lightstone, G. Lohman, A. Storm, C. Garcia-Arellano, and S. Fadden, "DB2 Design Advisor: Integrated Automatic Physical Database Design," in Proceedings of the 30th VLDB Conference, Toronto, Canada, 2004, pp. 1087--1097. Google ScholarDigital Library
Index Terms
- Proactive database index tuning through data threshold prediction
Recommendations
Selection and pruning algorithms for bitmap index selection problem using data mining
DaWaK'07: Proceedings of the 9th international conference on Data Warehousing and Knowledge DiscoveryIndexing schemes are redundant structures offered by DBMSs to speed up complex queries. Two types of indices are available: monoattribute indices (B-tree, bitmap, hash, etc.) and multi-attribute indices (join indices, bitmap join indices). In relational ...
Pruning search space of physical database design
DEXA'07: Proceedings of the 18th international conference on Database and Expert Systems ApplicationsVery large databases and data warehouses require many optimization structures to speed up their queries. These structures can be classified into two main categories: (1) redundant structures like mono attribute indexes, multi-attribute indexes (bitmap ...
Automatically classifying database workloads
CIKM '02: Proceedings of the eleventh international conference on Information and knowledge managementThe type of the workload on a database management system (DBMS) is a key consideration in tuning the system. Allocations for resources such as main memory can be very different depending on whether the workload type is Online Transaction Processing (...
Comments