Aggregation in traditional database systems is performed in batch mode: a query is submitted, the system processes a large volume of data over a long period of time, and, eventually, the final answer is returned. This archaic approach is frustrating to users and has been abandoned in most other areas of computing. In this paper we propose a new online aggregation interface that permits users to both observe the progress of their aggregation queries and control execution on the fly. After outlining usability and performance requirements for a system supporting online aggregation, we present a suite of techniques that extend a database system to meet these requirements. These include methods for returning the output in random order, for providing control over the relative rate at which different aggregates are computed, and for computing running confidence intervals. Finally, we report on an initial implementation of online aggregation in POSTGRES.

Online aggregation

There has been extensive work in query optimization since the early ‘70s. It is hard to capture the breadth and depth of this large body of work in a short article. Therefore, I have decided to focus primarily on the optimization of SQL queries in relational database systems and present my biased and incomplete view of this field. The goal of this article is not to be comprehensive, but rather to explain the foundations and present samplings of significant work in this area. I would like to apologize to the many contributors in this area whose work I have failed to explicitly acknowledge due to oversight or lack of space. I take the liberty of trading technical precision for ease of presentation.

/pdf/an-overview-of-query-optimization-in-relational-systems-1kekyiwr1r.pdf

An overview of query optimization in relational systems

/pdf/an-overview-of-query-optimization-in-relational-systems-4m0qmvbj0o.pdf

An Overview of Query Optimization in Relational Systems (paper)

The Context Interchange strategy presents a novel perspective for mediated data access in which semantic conflicts among heterogeneous systems are not identified a priori, but are detected and reconciled by a context mediator through comparison of contexts axioms corresponding to the systems engaged in data exchange. In this article, we show that queries formulated on shared views, export schema, and shared “ontologies” can be mediated in the same way using the Context Interchange framework. The proposed framework provides a logic-based object-oriented formalsim for representing and reasoning about data semantics in disparate systems, and has been validated in a prototype implementation providing mediated data access to both traditional and web-based information sources.

/pdf/context-interchange-new-features-and-formalisms-for-the-42rgjb0nk1.pdf

Context interchange: new features and formalisms for the intelligent integration of information

Adaptive Query Processing surveys the fundamental issues, techniques, costs, and benefits of adaptive query processing. It begins with a broad overview of the field, identifying the dimensions of adaptive techniques. It then looks at the spectrum of approaches available to adapt query execution at runtime - primarily in a non-streaming context. The emphasis is on simplifying and abstracting the key concepts of each technique, rather than reproducing the full details available in the papers. The authors identify the strengths and limitations of the different techniques, demonstrate when they are most useful, and suggest possible avenues of future research. Adaptive Query Processing serves as a valuable reference for students of databases, providing a thorough survey of the area. Database researchers will benefit from a more complete point of view, including a number of approaches which they may not have focused on within the scope of their own research.

/pdf/adaptive-query-processing-1iu6uhun8s.pdf

Adaptive Query Processing

Magic sets rewriting is a well-known optimization heuristic for complex decision-support queries. There can be many variants of this rewriting even for a single query, which differ greatly in execution performance. We propose cost-based techniques for selecting an efficient variant from the many choices.Our first contribution is a practical scheme that models magic sets rewriting as a special join method that can be added to any cost-based query optimizer. We derive cost formulas that allow an optimizer to choose the best variant of the rewriting and to decide whether it is beneficial. The order of complexity of the optimization process is preserved by limiting the search space in a reasonable manner. We have implemented this technique in IBM's DB2 C/S V2 database system. Our performance measurements demonstrate that the cost-based magic optimization technique performs well, and that without it, several poor decisions could be made.Our second contribution is a formal algebraic model of magic sets rewriting, based on an extension of the multiset relational algebra, which cleanly defines the search space and can be used in a rule-based optimizer. We introduce the multiset θ-semijoin operator, and derive equivalence rules involving this operator. We demonstrate that magic sets rewriting for non-recursive SQL queries can be modeled as a sequential composition of these equivalence rules.

/pdf/cost-based-optimization-for-magic-algebra-and-implementation-7j5dfuackd.pdf

Cost-based optimization for magic: algebra and implementation

Query rewrite optimization rules in IBM DB2 universal database

Many applications handle time-varying data. In this paper, we discuss how existing features of IBM DB2 for AIX Version 2 can be used to support this type of applications efficiently, in terms of both storage and query evaluation1. We discuss several temporal data models, the tradeoffs among these models, and how one can formulate so-called “as of” or “time-slice” queries in these models. We also show how “time-coalesce” operation can be expressed directly in SQL. In all these features, the recursive SQL capability supported by DB2 plays a crucial role. While DB2 lacks many temporal features proposed in the literature, we show to what extent existing features of a commercial DBMS, such as DB2, can be used to provide some advanced supports for time-varying data.

T. Y. Cliff Leung

Papers

Cost-based optimization for magic: algebra and implementation

Query rewrite optimization rules in IBM DB2 universal database

Querying Historical Data in IBM DB2 C/S DBMS Using Recursive SQL