Abstract
A variety of developments combine to highlight the need for respecting order when manipulating relations. For example, new functionality is being added to SQL to support OLAP-style querying in which order is frequently an important aspect. The set- or multiset-based frameworks for query optimization that are currently being taught to database students are increasingly inadequate.This paper presents a foundation for query optimization that extends existing frameworks to also capture ordering. A list-based relational algebra is provided along with three progressively stronger types of algebraic equivalences, concrete query transformation rules that obey the different equivalences, and a procedure for determining which types of transformation rules are applicable for optimizing a query. The exposition follows the style chosen by many textbooks, making it relatively easy to teach this material in continuation of the material covered in the textbooks, and to integrate this material into the textbooks.
- {Alb91} J. Albert. Algebraic Properties of Bag Data Types. In Proc. VLDB, pp. 211-219 (1991). Google ScholarDigital Library
- {CK97} M. J. Carey and D. Kossmann. Processing Top N and Bottom N Queries. Data Engineering Bulletin, 20(3):12-19 (1997).Google Scholar
- {DGK82} U. Dayal, N. Goodman, and R. H. Katz. An Extended Relational Algebra with Control over Duplicate Elimination. In Proc. PODS, pp. 117-123 (1982). Google ScholarDigital Library
- {GMc93} G. Graefe and W. J. McKenna. The Volcano Optimizer Generator: Extensibility and Efficient Search. In Proc. IEEE ICDE, pp. 209-218 (1993). Google ScholarDigital Library
- {GUW00} H. Garcia-Molina, J. D. Ullman, and J. Widom. Database System Implementation. Prentice Hall (2000).Google Scholar
- {IBM} DB2 Universal Database and DB2 Connect for Windows, OS/2 and Unix. Administration Guide. ⟨www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7d0frm3toc.htm⟩, current as of August 2, 2001.Google Scholar
- {Kie85} W. Kiessling. On Semantic Reefs and Efficient Processing of Correlation Queries with Aggregates. In Proc. VLDB, pp. 241-249 (1985).Google Scholar
- {Klu82} A. Klug. Equivalence of Relational Algebra and Relational Calculus Query Languages Having Aggregate Functions. JACM, 29(3): 699-717 (1982). Google ScholarDigital Library
- {PLH97} H. Pirahesh, T. Y. C. Leung, and W. Hasan. A Rule Engine for Query Transformation in Starburst and IBM DB2 C/S DBMS. In Proc. IEEE ICDE, pp. 391-400 (1997). Google ScholarDigital Library
- {Mic} Microsoft SQL Server Product Documentation. ⟨www.microsoft.com/sql/techinfo/productdoc/2000/⟩, current as of July 27, 2001.Google Scholar
- {OraDev} Oracle8i Application Developer's Guide - Fundamentals. ⟨technet.oracle.com/doc/server.815/a68003/toc.htm⟩, current as of July 27, 2001.Google Scholar
- {Ric92} J. Richardson. Supporting Lists in a Data Model (A Timely Approach). In Proc. VLDB, pp. 127-138 (1992). Google ScholarDigital Library
- {SJS99} G. Slivinskas, C. S. Jensen, and R. T. Snodgrass. Query Plans for Conventional and Temporal Queries Involving Duplicates and Ordering. TIMECENTER TR-49 (1999). ⟨www.cs.auc.dk/TimeCenter⟩, current as of July 27, 2001.Google Scholar
- {SJS01} G. Slivinskas, C. S. Jensen, and R. T. Snodgrass. A Foundation for Conventional and Temporal Query Optimization Addressing Duplicates and Ordering. IEEE TKDE, 13(1):21-49 (2001). Google ScholarDigital Library
- {SLR94} P. Seshadri, M. Livny, and R. Ramakrishnan. Sequence Query Processing. In Proc. ACM SIGMOD, pp. 430-441 (1994). Google ScholarDigital Library
- {SLR95} P. Seshadri, M. Livny, and R. Ramakrishnan. SEQ: A Model for Sequence Databases. In Proc. IEEE ICDE, pp. 232-239 (1994). Google ScholarDigital Library
Index Terms
- Bringing order to query optimization
Recommendations
Query optimization in multidatabase systems
CASCON '92: Proceedings of the 1992 conference of the Centre for Advanced Studies on Collaborative research - Volume 2A multidatabase system (MDBS) integrates information from autonomous local databases managed by heterogeneous database management systems (DBMS) in a distributed environment. For a query involving more than one database, global query optimization should ...
Synopses for query optimization: A space-complexity perspective
Special Issue: SIGMOD/PODS 2004Database systems use precomputed synopses of data to estimate the cost of alternative plans during query optimization. A number of alternative synopsis structures have been proposed, but histograms are by far the most commonly used. While histograms ...
Comments