Self-selecting, self-tuning, incrementally optimized indexes
Graefe, Goetz; Kuno, Harumi
Keyword(s): database index, adaptive, autonomic, query execution
Abstract: In a relational data warehouse with many tables, the number of possible and promising indexes exceeds human comprehension and requires automatic index tuning. While monitoring and reactive index tuning have been proposed, adaptive indexing focuses on adapting the physical data-base layout for and by actual queries. "Database cracking" is one such technique. Only if and when a column is used in query predicates, an index for the column is created; and only if and when a key range is queried, the index is optimized for this key range. The effect is akin to a sort that is adaptive and incremental. This sort is, however, very inefficient, particularly when applied on block-access devices. In contrast, traditional index creation sorts data with an efficient merge sort optimized for block- access devices, but it is neither adaptive nor incremental. We propose adaptive merging, an adaptive, incremental, and efficient technique for index creation. Index optimization focuses on key ranges used in actual queries. The resulting index adapts more quickly to new data and to new query patterns than database cracking. Sort efficiency is comparable to that of traditional B-tree creation. Nonetheless, the new technique promises better query performance than database cracking, both in memory and on block- access storage.
Additional Publication Information: To be published and presented at EDBT 2010, Lausanne, Switzerland, March 22-26, 2010.
External Posting Date: February 6, 2010 [Fulltext]. Approved for External Publication
Internal Posting Date: February 6, 2010 [Fulltext]