Proactive database index tuning through data threshold prediction

  • Authors:

  • Jeff Magnusson;Douglas D. Dankel, II

  • Affiliations:

  • University of Florida, Gainesville, FL;University of Florida, Gainesville, FL

  • Venue:
  • Proceedings of the 47th Annual Southeast Regional Conference
  • Year:
  • 2009

Quantified Score

Hi-index 0.00

Visualization

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.