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.

This paper describes the concepts used in the implementation of DBDSGN, an experimental physical design tool for relational databases developed at the IBM San Jose Research Laboratory. Given a workload for System R (consisting of a set of SQL statements and their execution frequencies), DBDSGN suggests physical configurations for efficient performance. Each configuration consists of a set of indices and an ordering for each table. Workload statements are evaluated only for atomic configurations of indices, which have only one index per table. Costs for any configuration can be obtained from those of the atomic configurations. DBDSGN uses information supplied by the System R optimizer both to determine which columns might be worth indexing and to obtain estimates of the cost of executing statements in different configurations. The tool finds efficient solutions to the index-selection problem; if we assume the cost estimates supplied by the optimizer are the actual execution costs, it finds the optimal solution. Optionally, heuristics can be used to reduce execution time. The approach taken by DBDSGN in solving the index-selection problem for multiple-table statements significantly reduces the complexity of the problem. DBDSGN’s principles were used in the Relational Design Tool (RDT), an IBM product based on DBDSGN, which performs design for SQL/DS, a relational system based on System R. System R actually uses DBDSGN’s suggested solutions as the tool expects because cost estimates and other necessary information can be obtained from System R using a new SQL statement, the EXPLAIN statement. This illustrates how a system can export a model of its internal assumptions and behavior so that other systems (such as tools) can share this model.

In this paper we focus on randomised evolutionary optimisation. We introduce a general framework for the optimisation of data models, based on the concept of evolution. This evolution is guided by a randomised schema mutator. Although our approach is expressed in terms of database optimisation, our ideas are applicable to other fields of randomised evolutionary optimisation of computer models, especially when similar (graph structured) models are used. Keywords and phrases: global optimisation, evolutionary optimisation, adaptive search, randomised algorithms, conceptual data models, transformation of data models, database optimisation. 1 Introduction 1.1 Intention of the paper In this paper we describe the underlying algorithm of a Prototype Evolutionary Database Optimiser, under development at the Department of Information Systems, University of Nijmegen, The Netherlands. The paper has three main contributions. Firstly, we introduce a formal framework for nondeterministic evolution...

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...

WARP is a system that helps users and administrators of web applications recover from intrusions such as SQL injection, cross-site scripting, and clickjacking attacks, while preserving legitimate user changes. WARP repairs from an intrusion by rolling back parts of the database to a version before the attack, and replaying subsequent legitimate actions. WARP allows administrators to retroactively patch security vulnerabilities—i.e., apply new security patches to past executions—to recover from intrusions without requiring the administrator to track down or even detect attacks. WARP’s timetravel database allows fine-grained rollback of database rows, and enables repair to proceed concurrently with normal operation of a web application. Finally, WARP captures and replays user input at the level of a browser’s DOM, to recover from attacks that involve a user’s browser. For a web server running MediaWiki, WARP requires no application source code changes to recover from a range of common web application vulnerabilities with minimal user input at a cost of 24–27 % in throughput and 2–3.2 GB/day in storage.

Compressing years of performance tuning experience into seconds of execution