In addition to indexes and materialized views, horizontal and vertical partitioning are important aspects of physical design in a relational database system that significantly impact performance. Horizontal partitioning also provides manageability; database administrators often require indexes and their underlying tables partitioned identically so as to make common operations such as backup/restore easier. While partitioning is important, incorporating partitioning makes the problem of automating physical design much harder since: (a) The choices of partitioning can strongly interact with choices of indexes and materialized views. (b) A large new space of physical design alternatives must be considered. (c) Manageability requirements impose a new constraint on the problem. In this paper, we present novel techniques for designing a scalable solution to this integrated physical design problem that takes both performance and manageability into account. We have implemented our techniques and evaluated it on Microsoft SQL Server. Our experiments highlight: (a) the importance of taking an integrated approach to automated physical design and (b) the scalability of our techniques. 1.

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 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. (c) Choose a subset from the candidate indexes in (b). Unfortunately, such a strategy can result in significant storage and index maintenance cost. 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 overhead, 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 by virtue of index merging, through experiments on Microsoft SQL Server 7.0. 1.