Index merging

Abstract

Indexes play a vital role in decision support systems by reducing the cost of answering complex queries. A popular methodology for choosing indexes that is adopted by database administrators as well as by automatic tools is: (a) consider poorly performing queries in the workload; (b) for each query, propose a set of candidate indexes that potentially benefits the query; and (c) choose a subset from the candidate indexes in (b). Unfortunately, such a strategy can result in significant storage and index maintenance costs. In this paper, we present a novel technique, called index merging, to address the above shortcoming. Index merging can take an existing set of indexes (perhaps optimized for individual queries in the workload) and produce a new set of indexes with significantly lower storage and maintenance overheads, while retaining almost all the querying benefits of the initial set of indexes. We present an efficient algorithm for index merging and demonstrate significant savings in index storage and maintenance through experiments on Microsoft SQL Server 7.0.