Self-selecting, self-tuning, incrementally optimized indexes

Graefe, Goetz ; Kuno, Harumi

HP Development Company

关键词: database index;    adaptive;    autonomic;    query execution;   

RP-ID  :  HPL-2010-24

学科分类：计算机科学（综合）

美国|英语

来源: HP Labs

【 摘 要 】

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.