Efficient methods of processing unanticipated queries are a crucial prerequisite for the success of generalized database management systems. A wide variety of approaches to improve the performance of query evaluation algorithms have been proposed: logic-based and semantic transformations, fast implementations of basic operations, and combinatorial or heuristic algorithms for generating alternative access plans and choosing among them. These methods are presented in the framework of a general query evaluation procedure using the relational calculus representation of queries. In addition, nonstandard query optimization issues such as higher level query evaluation, query optimization in distributed databases, and use of database machines are addressed. The focus, however, is on query optimization in centralized database systems.

The hot-set model, characterizing the buffer requirements of relational queries, is presented. This model allows the system to determine the optimal buffer space to be allocated to a query; it can also be used by the query optimizer to derive efficient execution plans accounting for the available buffer space, and by a query scheduler to prevent thrashing. The hot-set model is compared with the working-set model. A simulation study is presented. Categories and Subject Descriptors: H.2.4 [Database Management]: Systems-query processing

We present techniques for optimizing queries in memory-resident database systems. Optimization techniques in memory-resident database systems differ significantly from those in conventional disk-resident database systems. In this paper we address the following aspects of query optimization in such systems and present specific solutions for them: (1) a new approach to developing a CPU-intensive cost model; (2) new optimization strategies for main-memory query processing; (3) new insight into join algorithms and access structures that take advantage of memory residency of data; and (4) the effect of the operating system’s scheduling algorithm on the memory-residency assumption. We present an interesting result that a major cost of processing queries in memory-resident database systems is incurred by evaluation of predicates. We discuss optimization techniques using the Office-by-Example (OBE) that has been under development at IBM Research. We also present the results of performance measurements, which prove to be excellent in the current state of the art. Despite recent work on memory-resident database systems, query optimization aspects in these systems have not been well studied. We believe this paper opens the issues of query optimization in memory-resident database systems and presents practical solutions to them.

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

The object identifier index of an object-oriented database system is typically 20 % of the size of the database itself, and for large databases, only a small part of the index fits in main memory. To avoid index retrievals becoming a bottleneck, efficient buffering strategies are needed to minimize the number of disk accesses. In this report, we develop analytical cost models which we use to find optimal sizes of index page buffer and index entry cache, for different memory sizes, index sizes, and access patterns. Because existing buffer hit estimation models are not applicable for index page buffering in the case of tree based indexes, we have also developed an analytical model for index page buffer performance. The cost gain from using the results in this report is typically in the order of 200-300%. Thus, the results should be of valuable use in optimizers and tools for configuration and tuning of object-oriented database systems. 1

We consider two problems in distributed databases that have identical probabilistic structure, both of which have received significant attention in the literature. One is the problem of characterizing the number of distinct sites accessed by transactions in a distributed database and the other is the problem of determining the number of block accesses in a relation. We focus in particular on obtaining the distribution of this number when accesses are generated randomly. Previously published research has derived the mean number of sites or blocks accessed under some assumptions about the system parameters. The results presented in this paper generalize this work in several ways. First, we weaken the standard uniformity assumption to allow for a transaction accessing a random number of distinct sites or blocks and also consider a non-uniform access pattern in which one site or block (a `hotspot') is accessed more frequently than others. Second, we compute not only the mean and variance...

Cost models are powerful tools for analyzing algorithms, and important in cost-based query optimization. With increasing amounts of main memory available, it is important to include buffer performance in the models. In this paper, we describe and validate 1) buffer models for fine-granularity caching in buffers with locking, and 2) a buffer model for nodes in multiway-tree indexes in the context of unclustered accesses and non-uniform access patterns. The validations show a high accuracy of the models. In future self-tuning database systems, the importance of such models will be even higher than today, and the models presented in this paper should be ideally suited for use in these applications.

This work was supported by the Defense Advanced Research Project Agency under the KBMS Project, Contract N39-80-G-0132. Permission to copy without fee all or part of this material is granted provided that the copies are not made or distributed for direct commercial advantage, the ACM copyright notice and the title of the publication and its date appear, and notice is given that copying is by permission of the Association for Computing Machinery. To copy otherwise, or to republish, requires a fee and/or specific