Lightning fast and space efficient inequality joins

Zuhair Khayyat, William Lucia, Meghna Singh, Mourad Ouzzani, Paolo Papotti, Jorge Arnulfo Quiane Ruiz, Nan Tang, Panos Kalnis

Research output: Chapter in Book/Report/Conference proceedingChapter

14 Citations (Scopus)

Abstract

Inequality joins, which join relational tables on inequality conditions, are used in various applications. While there have been a wide range of optimization methods for joins in database systems, from algorithms such as sort-merge join and band join, to various indices such as B+-tree, R*-tree and Bitmap, inequality joins have received little attention and queries containing such joins are usually very slow. In this paper, we introduce fast inequality join algorithms. We put columns to be joined in sorted arrays and we use per- mutation arrays to encode positions of tuples in one sorted array w.r.t. the other sorted array. In contrast to sort-merge join, we use space efficient bit-arrays that enable optimiza- tions, such as Bloom filter indices, for fast computation of the join results. We have implemented a centralized version of these algorithms on top of PostgreSQL, and a distributed version on top of Spark SQL. We have compared against well known optimization techniques for inequality joins and show that our solution is more scalable and several orders of magnitude faster.

Original languageEnglish
Title of host publicationProceedings of the VLDB Endowment
PublisherAssociation for Computing Machinery
Pages2074-2085
Number of pages12
Volume8
Edition13
Publication statusPublished - 2015
Event3rd Workshop on Spatio-Temporal Database Management, STDBM 2006, Co-located with the 32nd International Conference on Very Large Data Bases, VLDB 2006 - Seoul, Korea, Republic of
Duration: 11 Sep 200611 Sep 2006

Other

Other3rd Workshop on Spatio-Temporal Database Management, STDBM 2006, Co-located with the 32nd International Conference on Very Large Data Bases, VLDB 2006
CountryKorea, Republic of
CitySeoul
Period11/9/0611/9/06

Fingerprint

Lightning
Electric sparks

ASJC Scopus subject areas

  • Computer Science (miscellaneous)
  • Computer Science(all)

Cite this

Khayyat, Z., Lucia, W., Singh, M., Ouzzani, M., Papotti, P., Quiane Ruiz, J. A., ... Kalnis, P. (2015). Lightning fast and space efficient inequality joins. In Proceedings of the VLDB Endowment (13 ed., Vol. 8, pp. 2074-2085). Association for Computing Machinery.

Lightning fast and space efficient inequality joins. / Khayyat, Zuhair; Lucia, William; Singh, Meghna; Ouzzani, Mourad; Papotti, Paolo; Quiane Ruiz, Jorge Arnulfo; Tang, Nan; Kalnis, Panos.

Proceedings of the VLDB Endowment. Vol. 8 13. ed. Association for Computing Machinery, 2015. p. 2074-2085.

Research output: Chapter in Book/Report/Conference proceedingChapter

Khayyat, Z, Lucia, W, Singh, M, Ouzzani, M, Papotti, P, Quiane Ruiz, JA, Tang, N & Kalnis, P 2015, Lightning fast and space efficient inequality joins. in Proceedings of the VLDB Endowment. 13 edn, vol. 8, Association for Computing Machinery, pp. 2074-2085, 3rd Workshop on Spatio-Temporal Database Management, STDBM 2006, Co-located with the 32nd International Conference on Very Large Data Bases, VLDB 2006, Seoul, Korea, Republic of, 11/9/06.
Khayyat Z, Lucia W, Singh M, Ouzzani M, Papotti P, Quiane Ruiz JA et al. Lightning fast and space efficient inequality joins. In Proceedings of the VLDB Endowment. 13 ed. Vol. 8. Association for Computing Machinery. 2015. p. 2074-2085
Khayyat, Zuhair ; Lucia, William ; Singh, Meghna ; Ouzzani, Mourad ; Papotti, Paolo ; Quiane Ruiz, Jorge Arnulfo ; Tang, Nan ; Kalnis, Panos. / Lightning fast and space efficient inequality joins. Proceedings of the VLDB Endowment. Vol. 8 13. ed. Association for Computing Machinery, 2015. pp. 2074-2085
@inbook{0bf030d7dcf947afa03c29a9a45bf372,
title = "Lightning fast and space efficient inequality joins",
abstract = "Inequality joins, which join relational tables on inequality conditions, are used in various applications. While there have been a wide range of optimization methods for joins in database systems, from algorithms such as sort-merge join and band join, to various indices such as B+-tree, R*-tree and Bitmap, inequality joins have received little attention and queries containing such joins are usually very slow. In this paper, we introduce fast inequality join algorithms. We put columns to be joined in sorted arrays and we use per- mutation arrays to encode positions of tuples in one sorted array w.r.t. the other sorted array. In contrast to sort-merge join, we use space efficient bit-arrays that enable optimiza- tions, such as Bloom filter indices, for fast computation of the join results. We have implemented a centralized version of these algorithms on top of PostgreSQL, and a distributed version on top of Spark SQL. We have compared against well known optimization techniques for inequality joins and show that our solution is more scalable and several orders of magnitude faster.",
author = "Zuhair Khayyat and William Lucia and Meghna Singh and Mourad Ouzzani and Paolo Papotti and {Quiane Ruiz}, {Jorge Arnulfo} and Nan Tang and Panos Kalnis",
year = "2015",
language = "English",
volume = "8",
pages = "2074--2085",
booktitle = "Proceedings of the VLDB Endowment",
publisher = "Association for Computing Machinery",
edition = "13",

}

TY - CHAP

T1 - Lightning fast and space efficient inequality joins

AU - Khayyat, Zuhair

AU - Lucia, William

AU - Singh, Meghna

AU - Ouzzani, Mourad

AU - Papotti, Paolo

AU - Quiane Ruiz, Jorge Arnulfo

AU - Tang, Nan

AU - Kalnis, Panos

PY - 2015

Y1 - 2015

N2 - Inequality joins, which join relational tables on inequality conditions, are used in various applications. While there have been a wide range of optimization methods for joins in database systems, from algorithms such as sort-merge join and band join, to various indices such as B+-tree, R*-tree and Bitmap, inequality joins have received little attention and queries containing such joins are usually very slow. In this paper, we introduce fast inequality join algorithms. We put columns to be joined in sorted arrays and we use per- mutation arrays to encode positions of tuples in one sorted array w.r.t. the other sorted array. In contrast to sort-merge join, we use space efficient bit-arrays that enable optimiza- tions, such as Bloom filter indices, for fast computation of the join results. We have implemented a centralized version of these algorithms on top of PostgreSQL, and a distributed version on top of Spark SQL. We have compared against well known optimization techniques for inequality joins and show that our solution is more scalable and several orders of magnitude faster.

AB - Inequality joins, which join relational tables on inequality conditions, are used in various applications. While there have been a wide range of optimization methods for joins in database systems, from algorithms such as sort-merge join and band join, to various indices such as B+-tree, R*-tree and Bitmap, inequality joins have received little attention and queries containing such joins are usually very slow. In this paper, we introduce fast inequality join algorithms. We put columns to be joined in sorted arrays and we use per- mutation arrays to encode positions of tuples in one sorted array w.r.t. the other sorted array. In contrast to sort-merge join, we use space efficient bit-arrays that enable optimiza- tions, such as Bloom filter indices, for fast computation of the join results. We have implemented a centralized version of these algorithms on top of PostgreSQL, and a distributed version on top of Spark SQL. We have compared against well known optimization techniques for inequality joins and show that our solution is more scalable and several orders of magnitude faster.

UR - http://www.scopus.com/inward/record.url?scp=84952793785&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=84952793785&partnerID=8YFLogxK

M3 - Chapter

VL - 8

SP - 2074

EP - 2085

BT - Proceedings of the VLDB Endowment

PB - Association for Computing Machinery

ER -