This paper introduces the concept of letting an RDBMS Optimizer optimize its own environment. In our project, we have used the DB2 Optimizer to tackle the index selection problem, a variation of the knapack problem. This paper will discuss our implementation of index recommendation, the user interface, and provide measurements on the quality of the recommended indexes. 1.

Stringent performance requirements in DB applications have led to the use of parallelism for database processing. To allow the database system to take advantage of the performance of parallel shared-nothing systems, the physical DB design must be appropriate for the DB structure and the workload. We develop decision algorithms that will select a good physical DB design both when the DB is first loaded into the system (static decision) and while the DB is being used by the workload (dynamic decision). Our decision algorithms take the database structure, workload, and system characteristics as inputs. The static (or initial) physical DB design decision algorithm involves: • selecting a partitioning attribute for each relation that determines how the relation is fragmented across the nodes (allowing for high I/O bandwidth); • selecting indexes on the relation attributes to allow faster accesses compared to sequential file scans; • selecting the attributes by which to cluster a relation in order to take advantage of the prefetching and caching involved in I/O access; • grouping of relations to allow DB operations (joins) on relation pairs to be executed locally

An important problem in the physical design of databases is the selection of secondary indices. In general, this problem can not be solved in an optimal way due to the complexity of the selection process. Often use is made of heuristics such as the well-known ADD and DROP algorithms. In this paper it will be shown that frequently used cost functions can be classified as super- or submodular functions. For these functions several mathematical properties have been derived which reduce the complexity of the index selection problem. These properties will be used to develop a tool for physical database design and also give a mathematical foundation for the success of the before-mentioned ADD and DROP algorithms. Keywords: Physical database design, Secondary index selection, ADD and DROP algorithms, Supermodular functions, Submodular functions. 1 Introduction Physical database design is an important step in designing databases and aims to generate efficient storage structures for the data....

This paper considers the problem of minimizing the response time for a given database workload by a proper choice of indexes. This problem is NP-hard and known in the literature as the Index Selection Problem (ISP).

The Index Selection Problem (ISP) is a phase of fundamental importance in the physical design of databases, calling for a set of indexes to be built in a database so as to minimize the overall execution time for a given database workload. The problem is a generalization of the well-known Uncapacitated Facility Location Problem (UFLP). In [6], we formulate ISP as a set packing problem, showing that our mathematical model contains all the clique inequalities, and describe a branch-and-cut algorithm based on the separation of odd-hole inequalities. In this paper, we describe an effective exact separation procedure for a suitably-defined family of lifted odd-hole inequalities, obtained by applying a Chvátal-Gomory derivation to the clique inequalities. Our analysis goes in the direction of determining a new class of inequalities over which ecient separation is possible, rather than introducing new classes of (facet-de ning) inequalities that later turn out to be difficult to separate. Our separation procedure is embedded within our branch-and-cut algorithm for the exact solution of ISP. Computational results on two different classes of instances are given, showing the e ectiveness of the new approach. We also test our algorithm on UFLP instances both taken from the literature and randomly generated.

Intending to develop a tool which aims to support the physical design of relational databases can not be done without considering the problem of index selection. Generally the problem is split into a primary and secondary index selection problem and the selection is done per table. Whereas much attention has been paid on the selection of secondary indices relatively less is known about the selection of a primary index and the relation between them. These are exactly the topics of this paper. 1 Introduction At the University of Twente in cooperation with the G.A.K. a tool is being developed which aims to support the physical design of relational databases [2]. A problem of considerable interest in the physical design of databases is the selection of a good set of indices. Indices can be considered as auxiliary files that allow to retrieve tuples satisfying certain selection predicates without having to examine the whole relation. On the other hand, updating the database causes an index...

The Index Selection Problem (ISP) is a phase of fundamental importance in the physical design of databases, calling for a set of indexes to be built in a database so as to minimize the overall execution time for a given database workload. The problem is a generalization of the well-known Uncapacitated Facility Location Problem (UFLP). We formulate ISP as a set packing problem, showing that our mathematical model contains all the clique inequalities (whose number is polynomial). The upper bound provided by solving the LP relaxation of this model turns out to be quite bad for some instances. We therefore face the problem of strengthening this relaxation by means of additional valid inequalities. In particular, we describe an effective exact separation procedure for a suitably-defined family of lifted odd-hole inequalities, obtained by applying a Chvatal-Gomory derivation to the clique inequalities. These results are used within a branch-and-cut algorithm for the exact soluti...

Compressing years of performance tuning experience into seconds of execution

We survey the main results presented in the author's Ph.D Thesis [12], which addresses the use of Linear Programming (LP) relaxations within exact and heuristic algorithms for the solution of some Combinatorial Optimization (CO) problems arising from real-life applications. It is well known that CO problems admit several possible Integer LP (ILP) formulations, and the corresponding LP relaxations may provide bounds of quite different quality. In all cases considered in the thesis, the dimension of the formulations used, i.e., the number of constraints and/or variables, is so large to make a black-box use of an LP solver completely impractical. The main practical application of the work presented in the thesis is the design of the algorithmic part of the crew management system of the Italian railways. In particular, we deal with the effective solution of very large set covering and crew rostering problems. We also present novel methodologies and results which may have some impact in the solution of a wide spectrum of practical problems, and effective exact algorithms for two important real-world CO problems, namely the problem of selecting the indexes to be constructed in a database and the problem of sorting a permutation by the minimum number of reversals, which has application in genome rearrangements.

Several indexing techniques have been proposed to accelerate database operations in object-oriented databases. A database operation gives rise to the processing of a path. We address the problem of optimal index configuration for a given logical schema, the workload defined on the schema and some other database characteristics such as the cardinality of a class, page size, etc. As will be shown an optimal index configuration for the workload can be achieved by splitting the paths into subpaths and by indexing each subpath optimal. For the moment we take a limited number of existing indexing techniques into account (simple index, inherited index, nested inherited-index, multi-index, and multi-inherited index) and the possibility of not indexing a (sub)path. However, the principles of the algorithm will remain the same adding more indexing techniques. 1 Introduction Object-oriented data models are based on some fundamentals concepts which will be discussed briefly. A real world entity i...