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.

In a recent paper, we proposed adding a STOP AFTER clause to SQL to permit the cardinality of a query result to be explicitly limited by query writers and query tools. We demonstrated the usefulness of having this clause, showed how to extend a traditional cost-based query optimizer to accommodate it, and demonstrated via DB2-based simulations that large performance gains are possible when STOP AFTER queries are explicitly supported by the database engine. In this paper, we present several new strategies for efficiently processing STOP AFTER queries. These strategies, based largely on the use of range partitioning techniques, offer significant additional savings for handling STOP AFTER queries that yield sizeable result sets. We describe classes of queries where such savings would indeed arise and present experimental measurements that show the benefits and tradeoffs associated with the new processing strategies. 1

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.

Abstract not found

In many relational database systems using a relational algebra-based query language, query optimization involves the syntactic modification of queries into a “canonical form”[Dat90], and then choosing from possibly several methods of evaluating the query. Semantic query optimization (SQO) is the idea of semantically transforming a query using additional schema information, such as integrity constraints. The query is passed through three different phases of logical transformation: standardization, simplification, and amelioration. The result of this process is a query that may, in fact, appear quite different from the original query posed by the user, but is guaranteed to return the same results given the same database instance. In this paper, we give a brief overview of the concepts of semantic query optimization, and present a survey of the literature. Most of the literature involves the application of SQO to relational systems, or to logic-based models such as Datalog[CGT89, Ull88, Ull89]. To show how these techniques may be used with Object-Oriented Database Management Systems (OODBMS), we attempt to apply Chakravarthy et al.’s[CGM90] idea of semantically constrained axioms to an extension of Datalog called Complex-Prolog[GR89]. Complex-Prolog supports the

1.1 General Remarks............................ 3

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

Query optimization in databases has gain a lot of importance in recent years. In this paper, we have analyzed different techniques of query optimization in relational databases and compared their performance. We have covered the techniques which use different methods for query representation.