Skip to main content
SpringerLink
Log in

Join operations in temporal databases

  • Regular Paper
  • Published:
The VLDB Journal Aims and scope Submit manuscript

Abstract.

Joins are arguably the most important relational operators. Poor implementations are tantamount to computing the Cartesian product of the input relations. In a temporal database, the problem is more acute for two reasons. First, conventional techniques are designed for the evaluation of joins with equality predicates rather than the inequality predicates prevalent in valid-time queries. Second, the presence of temporally varying data dramatically increases the size of a database. These factors indicate that specialized techniques are needed to efficiently evaluate temporal joins.

We address this need for efficient join evaluation in temporal databases. Our purpose is twofold. We first survey all previously proposed temporal join operators. While many temporal join operators have been defined in previous work, this work has been done largely in isolation from competing proposals, with little, if any, comparison of the various operators. We then address evaluation algorithms, comparing the applicability of various algorithms to the temporal join operators and describing a performance study involving algorithms for one important operator, the temporal equijoin. Our focus, with respect to implementation, is on non-index-based join algorithms. Such algorithms do not rely on auxiliary access paths but may exploit sort orderings to achieve efficiency.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Similar content being viewed by others

References

  1. Allen JF (1983) Maintaining knowledge about temporal intervals. Commun ACM 26(11):832-843

    Article  Google Scholar 

  2. Arge L, Procopiuc O, Ramaswamy S, Suel T, Vitter JS (1998) Scalable sweeping-based spatial join. In: Proceedings of the international conference on very large databases, New York, 24-27 August 1998, pp 570-581

  3. Beckmann N, Kriegel HP, Schneider R, Seeger B (1990) The R*-tree: an efficient and robust access method for points and rectangles. In: Proceedings of the ACM SIGMOD conference, Atlantic City, NJ, 23-25 May 1990, pp 322-331

  4. van den Bercken J, Seeger B (1996) Query processing techniques for multiversion access methods. In: Proceedings of the international conference on very large databases, Mubai (Bombay), India, 3-6 September 1996, pp 168-179

  5. Böhlen MH, Snodgrass RT, Soo MD (1997) Temporal coalescing. In: Proceedings of the international conference on very large databases, Athens, Greece, 25-29 August 1997, pp 180-191

  6. Clifford J, Croker A (1987) The historical relational data model (HRDM) and algebra based on lifespans. In: Proceedings of the international conference on data engineering, Los Angeles, 3-5 February 1987, pp 528-537. IEEE Press, New York

  7. Clifford J, Croker A (1993) The historical relational data model (HRDM) revisited. In: Tansel A, Clifford J, Gadia S, Jajodia S, Segev A, Snodgrass RT (eds) Temporal databases: theory, design, and implementation, ch 1. Benjamin/Cummings, Reading, MA, pp 6-27

  8. Clifford J, Uz Tansel A (1985) On an algebra for historical relational databases: two views. In: Proceedings of the ACM SIGMOD international conference on management of data, Austin, TX, 28-31 May 1985, pp 1-8

  9. DeWitt DJ, Katz RH, Olken F, Shapiro LD, Stonebraker MR, Wood D (1984) Implementation techniques for main memory database systems. In: Proceedings of the ACM SIGMOD international conference on management of data, Boston, 18-21 June 1984, pp 1-8

  10. Dittrich JP, Seeger B, Taylor DS, Widmayer P (2002) Progressive merge join: a generic and non-blocking sort-based join algorithm. In: Proceedings of the conference on very large databases, Madison, WI, 3-6 June 2002, pp 299-310

  11. Dunn J, Davey S, Descour A, Snodgrass RT (2002) Sequenced subset operators: definition and implementation. In: Proceedings of the IEEE international conference on data engineering, San Jose, 26 February-1 March 2002, pp 81-92

  12. Dyreson CE, Snodgrass RT (1993) Timestamp semantics and representation. Inform Sys 18(3):143-166

    Google Scholar 

  13. Elmasri R, Wuu GTJ, Kim YJ (1990) The time index: an access structure for temporal data. In: Proceedings of the conference on very large databases, Brisbane, Queensland, Australia, [4] 13-16 August 1990, pp 1-12

  14. Etzion O, Jajodia S, Sripada S (1998) Temporal databases: research and practice. Lecture notes in computer science, vol 1399. Springer, Berlin Heidelberg New York

    Google Scholar 

  15. Gadia SK (1988) A homogeneous relational model and query languages for temporal databases. ACM Trans Database Sys 13(4):418-448

    Google Scholar 

  16. Gao D, Jensen CS, Snodgrass RT, Soo MD (2002) Join operations in temporal databases. TimeCenter TR-71 http://www.cs.auc.dk/TimeCenter/pub.htm

  17. Gao D, Kline N, Soo MD, Dunn J (2002) TimeIT: the Time integrated testbed, v. 2.0 Available via anonymous FTP at: ftp.cs.arizona.edu

    Google Scholar 

  18. Graefe G (1993) Query evaluation techniques for large databases. ACM Comput Surv 25(2):73-170

    Google Scholar 

  19. Graefe G, Linville A, Shapiro LD (1994) Sort vs. hash revisited. IEEE Trans Knowl Data Eng 6(6):934-944

    Google Scholar 

  20. Gunadhi H, Segev A (1991) Query processing algorithms for temporal intersection joins. In: Proceedings of the IEEE conference on data engineering, Kobe, Japan, 8-12 April 1991, pp 336-344

  21. Guttman A (1984) R-trees: a dynamic index structure for spatial searching. In: Proceedings of the ACM SIGMOD conference, Boston, 18-21 June 1984, pp 47-57

  22. Harris EP, Ramamohanarao K (1996) Join algorithm costs revisited. J Very Large Databases 5(1):64-84

    Google Scholar 

  23. Jensen CS (ed) (1998) The consensus glossary of temporal database concepts - February 1998 version. In [14], pp 367-405

  24. Jensen CS, Mark L, Roussopoulos N (1991) Incremental implementation model for relational databases with transaction time. IEEE Trans Knowl Data Eng 3(4):461-473

    Google Scholar 

  25. Jensen CS, Snodgrass RT, Soo MD (1996) Extending existing dependency theory to temporal databases. IEEE Trans Knowl Data Eng 8(4):563-582

    Google Scholar 

  26. Jensen CS, Soo MD, Snodgrass RT (1994) Unifying temporal models via a conceptual model. Inform Sys 19(7):513-547

    Google Scholar 

  27. Leung TY, Muntz R (1990) Query processing for temporal databases. In: Proceedings of the IEEE conference on data engineering, Los Angeles, 6-10 February 1990, pp 200-208

  28. Leung TYC, Muntz RR (1992) Temporal query processing and optimization in multiprocessor database machines. In: Proceedings of the conference on very large databases, Vancouver, BC, Canada, pp 383-394

  29. Leung TYC, Muntz RR (1993) Stream processing: temporal query processing and optimization. In: Tansel A, Clifford J, Gadia S, Jajodia S, Segev A, Snodgrass RT (eds) Temporal databases: theory, design, and implementation, ch 14, Benjamin/Cummings, Reading, MA, pp 329-355

  30. Li W, Gao D, Snodgrass RT (2002) Skew handling techniques in sort-merge join. In: Proceedings of the ACM SIGMOD conference on management of data Madison, WI, 3-6 June 2002, pp 169-180

  31. Lo ML, Ravishankar CV (1994) Spatial joins using seeded trees. In: Proceedings of the ACM SIGMOD conference, Minneapolis, MN, 24-27 May 1994, pp 209-220

  32. Lo ML, Ravishankar CV (1996) Spatial hash-joins. In: Proceedings of ACM SIGMOD conference, Montreal, 4-6 June 1996, pp 247-258

  33. Lu H, Ooi BC, Tan KL (1994) On spatially partitioned temporal join. In: Proceedings of the conference on very large databases, Santiago de Chile, Chile, 12-15 September 1994, pp 546-557

  34. McKenzie E (1988) An algebraic language for query and update of temporal databases. Ph.D. dissertation, Department of Computer Science, University of North Carolina, Chapel Hill, NC

  35. Mishra P, Eich M (1992) Join processing in relational databases. ACM Comput Surv 24(1):63-113

    Google Scholar 

  36. Navathe S, Ahmed R (1993) Temporal extensions to the relational model and SQL. In: Tansel A, Clifford J, Gadia S, Jajodia S, Segev A, Snodgrass RT (eds) Temporal databases: theory, design, and implementation. Benjamin/Cummings, Reading, MA, pp 92-109

  37. Orenstein JA (1986) Spatial query processing in an object-oriented database system. In: Proceedings of the ACM SIGMOD conference, Washington, DC, 28-30 May 1986, [4] pp 326-336

  38. Orenstein JA, Manola FA (1988) PROBE spatial data modeling and query processing in an image database application. IEEE Trans Software Eng 14(5):611-629

    Google Scholar 

  39. Özsoyoglu G, Snodgrass RT (1995) Temporal and real-time databases: a survey. IEEE Trans Knowl Data Eng 7(4):513-532

  40. Patel JM, DeWitt DJ (1996) Partition based spatial-merge join. In: Proceedings of the ACM SIGMOD conference, Montreal, 4-6 June 1996, pp 259-270

  41. Pfoser D, Jensen CS (1999) Incremental join of time-oriented data. In: Proceedings of the international conference on scientific and statistical database management, Cleveland, OH, 28-30 July 1999, pp 232-243

  42. Ramakrishnan R, Gehrke J (2000) Database management systems. McGraw-Hill, New York

  43. Rana S, Fotouhi F (1993) Efficient processing of time-joins in temporal data bases. In: Proceedings of the international symposium on DB systems for advanced applications, Daejeon, South Korea, 6-8 April 1993, pp 427-432

  44. Salzberg B, Tsotras VJ (1999) Comparison of access methods for time-evolving data. ACM Comput Surv 31(2):158-221

    Google Scholar 

  45. Samet H (1990) The design and analysis of spatial data structures. Addison-Wesley, Reading, MA

  46. Segev A (1993) Join processing and optimization in temporal relational databases. In: Tansel A, Clifford J, Gadia S, Jajodia S, Segev A, Snodgrass RT (eds) Temporal databases: theory, design, and implementation, ch 15. Benjamin/Cummings, Reading, MA, pp 356-387

  47. Segev A, Gunadhi H (1989) Event-join optimization in temporal relational databases. In: Proceedings of the conference on very large databases, Amsterdam, 22-25 August 1989, pp 205-215

  48. Sellis T, Roussopoulos N, Faloutsos C (1987) The R+-tree: a dynamic index for multidimensional objects. In: Proceedings of the conference on very large databases, Brighton, UK, 1-4 September 1987, pp 507-518

  49. Sitzmann I, Stuckey PJ (2000) Improving temporal joins using histograms. In: Proceedings of the international conference on database and expert systems applications, London/Greenwich, UK, 4-8 September 2000, pp 488-498

  50. Slivinskas G, Jensen CS, Snodgrass RT (2001) A foundation for conventional and temporal query optimization addressing duplicates and ordering. Trans Knowl Data Eng 13(1):21-49

    Google Scholar 

  51. Snodgrass RT, Ahn I (1986) Temporal databases. IEEE Comput 19(9):35-42

    Google Scholar 

  52. Son D, Elmasri R (1996) Efficient temporal join processing using time index. In: Proceedings of the conference on statistical and scientific database management, Stockholm, Sweden, 18-20 June 1996, pp 252-261

  53. Soo MD, Jensen CS, Snodgrass RT (1995) An algebra for TSQL2. In: Snodgrass RT (ed) The TSQL2 temporal query language, ch 27, Kluwer, Amsterdam, pp 505-546

  54. Soo MD, Snodgrass RT, Jensen CS (1994) Efficient evaluation of the valid-time natural join. In: Proceedings of the international conference on data engineering, Houston, TX, 14-18 February 1994, pp 282-292

  55. Tsotras VJ, Kumar A (1996) Temporal database bibliography update. ACM SIGMOD Rec 25(1):41-51

    Google Scholar 

  56. Zhang D, Tsotras VJ, Seeger B (2002) Efficient temporal join processing using indices. In: Proceedings of the IEEE international conference on data engineering, San Jose, 26 February-1 March 2002, pp 103-113

  57. Zurek T (1997) Optimisation of partitioned temporal joins. Ph.D. Dissertation, Department of Computer Science, Edinburgh University, Edinburgh, UK

Download references

Author information

Authors and Affiliations

  1. Computer Science Department, University of Arizona, P.O. Box 210077, AZ 85721-0077, Tucson, USA

    Dengfeng Gao & Richard T. Snodgrass

  2. Department of Computer Science, Aalborg University, Fredrik Bajers Vej 7E, 9220, Aalborg Ø, Denmark

    Christian S. Jensen

  3. Amazon.com, Seattle

    Michael D. Soo

Authors
  1. Dengfeng Gao
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Christian S. Jensen
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Richard T. Snodgrass
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Michael D. Soo
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Dengfeng Gao.

Additional information

Received: 17 October 2002, Accepted: 26 July 2003, Published online: 28 October 2003

Edited by: T. Sellis

Rights and permissions

Reprints and permissions

About this article

Cite this article

Gao, D., Jensen, C.S., Snodgrass, R.T. et al. Join operations in temporal databases. The VLDB Journal 14, 2–29 (2005). https://doi.org/10.1007/s00778-003-0111-3

Download citation

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00778-003-0111-3

Keywords:

Search

Navigation