This Article 
 Bibliographic References 
 Add to: 
A Foundation for Conventional and Temporal Query Optimization Addressing Duplicates and Ordering
January/February 2001 (vol. 13 no. 1)
pp. 21-49

Abstract—Most real-world databases contain substantial amounts of time-referenced, or temporal, data. Recent advances in temporal query languages show that such database applications may benefit substantially from built-in temporal support in the DBMS. To achieve this, temporal query representation, optimization, and processing mechanisms must be provided. This paper presents a foundation for query optimization that integrates conventional and temporal query optimization and is suitable for both conventional DBMS architectures and ones where the temporal support is obtained via a layer on top of a conventional DBMS. This foundation captures duplicates and ordering for all queries, as well as coalescing for temporal queries, thus generalizing all existing approaches known to the authors. It includes a temporally extended relational algebra to which SQL and temporal SQL queries may be mapped, six types of algebraic equivalences, concrete query transformation rules that obey different equivalences, a procedure for determining which types of transformation rules are applicable for optimizing a query, and a query plan enumeration algorithm. The presented approach partitions the work required by the database implementor to develop a provably correct query optimizer into four stages: The database implementor has to 1) specify operations formally, 2) design and prove correct appropriate transformation rules that satisfy any of the six equivalence types, 3) augment the mechanism that determines when the different types of rules are applicable to ensure that the enumeration algorithm applies the rules correctly, and 4) ensure that the mapping generates a correct initial query plan.

[1] J. Albert, “Algebraic Properties of Bag Data Types,” Proc. Very Large Data Base Conf., pp. 211–219, 1991.
[2] J.F. Allen, “Maintaining Knowledge about Temporal Intervals,” Comm. ACM, vol. 26, no. 11, pp. 832–843, 1983.
[3] M.H. Böhlen, “The Tiger Temporal Database System,”, current as of 1 Dec. 2000.
[4] M.H. Böhlen, R. Busatto, and C.S. Jensen, “Point versus Interval-Based Temporal Data Models,” Proc. IEEE Int'l Conf. Data Eng., pp. 192–200, 1998.
[5] C. Bettini, C.E. Dyreson, W.S. Evans, R.T. Snodgrass, and X.S. Wang, “A Glossary of Time Granularity Concepts,” Temporal Databases: Research and Practice, pp. 406–413, 1998.
[6] M.H. Böhlen, C.S. Jensen, and R.T. Snodgrass, “Temporal Statement Modifiers,” ACM Trans. Database Systems, tentatively scheduled for vol. 25, no. 4, Dec. 2000.
[7] M.H. Böhlen, “Temporal Database System Implementations,” ACM SIGMOD Record, vol. 24, no. 4, pp. 53–60, 1995.
[8] M.H. Böhlen, R.T. Snodgrass, and M.D. Soo, “Coalescing in Temporal Databases,” Proc. Very Large Data Base Conf., pp. 180–191, 1996.
[9] J. Clifford, C.E. Dyreson, T. Isakowitz, C.S. Jensen, and R.T. Snodgrass, “On the Semantics of‘Now’in Databases,” ACM Trans. Database Systems, vol. 22, no. 2, pp. 171–214, 1997.
[10] U. Dayal, N. Goodman, and R.H. Katz, “An Extended Relational Algebra with Control over Duplicate Elimination,” Proc. ACM Symp. Principles of Database Systems, pp. 117–123, 1982.
[11] Temporal Databases: Research and Practice. O. Etzion, S. Jajodia, and S. Sripada, eds., Springer-Verlag, 1998.
[12] H. Garcia-Molina, J.D. Ullman, and J. Widom, Database System Implementation. Prentice Hall, 2000.
[13] S.K. Gadia and S.S. Nair, “Algebraic Identities and Query Optimization in a Parametric Model for Relational Temporal Databases,” IEEE Trans. Knowledge and Data Eng., vol. 10, no. 5, pp. 793–807, 1998.
[14] M.J.C. Gordon, The Denotational Description of Programming Languages. Springer-Verlag, 1987.
[15] S. Grumbach and T. Milo, “Towards Tractable Algebras for Bags,” Proc. Symp. Principles of Database Systems, pp. 49–58, 1993.
[16] H. Gunadhi and A. Segev, “A Framework for Query Optimization in Temporal Databases,” Proc. Fifth Int'l Conf. Statistical and Scientific Database Management, pp. 131–147, 1990.
[17] W.H. Inmon, Building the Data Warehouse, second ed. John Wiley and Sons, 1996.
[18] “A Consensus Glossary of Temporal Database Concepts,” Temporal Databases: Research and Practice, C.S. Jensen and C.E. Dyreson, eds., pp. 367–405, 1998.
[19] C.S. Jensen and R.T. Snodgrass, “Temporal Data Management,” IEEE Trans. Knowledge and Data Eng., vol. 11, no. 1, pp. 36–45, 1999.
[20] C.S. Jensen, M.D. Soo, and R.T. Snodgrass, “Unifying Temporal Data Models via a Conceptual Model,” Information Systems, vol. 19, no. 7, pp. 513–547, 1994.
[21] W. Kiessling, “On Semantic Reefs and Efficient Processing of Correlation Queries with Aggregates,” Proc. Very Large Data Bases Conf., pp. 241–249, 1985.
[22] A. Klug, “Equivalence of Relational Algebra and Relational Calculus Query Languages Having Aggregate Functions,” J. ACM, vol. 29, no. 3, pp. 699–717, 1982.
[23] D.E. Knuth, “Semantics of Context-Free Languages,” Mathematical Systems Theory, Volume 2, pp. 127–145, June 1968.
[24] N. Kline and R.T. Snodgrass, “Computing Temporal Aggregates,” Proc. IEEE Int'l Conf. Data Eng., pp. 222–231, 1995.
[25] L. Libkin and L. Wong, “New Techniques for Studying Set Languages, Bag Languages, and Aggregate Functions,” Proc. ACM Symp. Principles of Database Systems, pp. 155–166, 1994.
[26] T.Y.C. Leung, H. Pirahesh, P. Seshadri, and J.M. Hellerstein, “Query Rewrite Optimization Rules in IBM DB/2 Universal Database,” Readings in Database Systems, third ed., M. Stonebraker and J. Hellerstein, eds., Morgan Kaufmann, pp. 153–168, 1998.
[27] T.Y.C. Leung and R.R. Muntz, “Stream Processing: Temporal Query Processing and Optimization,” Temporal Databases: Theory, Design, and Implementation, A.U. Tansel et al., eds., Benjamin/Cummings, pp. 329–355, 1993.
[28] L.E. McKenzie, Jr. and R.T. Snodgrass, “Evaluation of Relational Algebras Incorporating the Time Dimension in Databases,” ACM Computing Surveys, vol. 23, no. 4, pp. 501–543, 1991.
[29] I.S. Mumick, H. Pirahesh, and R. Ramakrishnan, “The Magic of Duplicates and Aggregates,” Proc. Very Large Data Base Conf., pp. 264–277, 1990.
[30] I.S. Mumick, S.J. Finkelstein, H. Pirahesh, and R. Ramakrishnan, “Magic is Relevant,” Proc. ACM SIGMOD, pp. 247–258, 1990.
[31] G. Özsoyovglu and R.T. Snodgrass, “Temporal and Real-Time Databases: A Survey,” IEEE Trans. Knowledge and Data Eng., vol. 7, no. 4, pp. 513–532, 1995.
[32] G. Slivinskas, C.S. Jensen, and R.T. Snodgrass, “Query Plans for Conventional and Temporal Queries Involving Duplicates and Ordering,” TimeCenterTechnical Report TR-49, 1999.
[33] R.T. Snodgrass, “The Temporal Query Language TQuel,” ACM Trans. Database Systems, vol. 12, no. 2, pp. 247–298, 1987.
[34] The TSQL2 Temporal Query Language. R.T. Snodgrass, ed., Kluwer Academic Publishers, 1995.
[35] R.T. Snodgrass, Developing Time-Oriented Database Applications in SQL. Morgan Kaufmann, 1999.
[36] K. Torp, C.S. Jensen, and R.T. Snodgrass, “Stratum Approaches to Temporal DBMS Implementation,” Proc. IEEE Int'l Database Eng. and Applications Symp., pp. 4–13, 1998.
[37] J. Yang, H.C. Ying, and J. Widom, “TIP: A Temporal Extension to Informix,” Proc. ACM SIGMOD, p. 596, 2000.
[38] C. Zaniolo, S. Ceri, C. Faloutsos, R.T. Snodgrass, V.S. Subrahmanian, and R. Zicari, Advanced Database Systems. Morgan Kaufmann Publishers, 1997.

Index Terms:
Temporal databases, query optimization, transformation rules, temporal algebra, duplicate elimination, coalescing.
Giedrius Slivinskas, Christian S. Jensen, Richard T. Snodgrass, "A Foundation for Conventional and Temporal Query Optimization Addressing Duplicates and Ordering," IEEE Transactions on Knowledge and Data Engineering, vol. 13, no. 1, pp. 21-49, Jan.-Feb. 2001, doi:10.1109/69.908979
Usage of this product signifies your acceptance of the Terms of Use.