Encyclopedia of Database Systems

Living Edition
| Editors: Ling Liu, M. Tamer Özsu

Join Index

  • Theodore JohnsonEmail author
Living reference work entry
DOI: https://doi.org/10.1007/978-1-4899-7993-3_892-3


A join index is a collection of pairs {(r, s)} such that the record in table R with record ID (RID) r joins with the record in table S with RID s, according to the join predicate which defines the index.

Key Points

The purpose of a join index is to accelerate common joins, even equijoins. One of the advantages of join indices is that they can be represented in a very compact way, allowing for highly efficient access. For example, suppose that the DBMS is to evaluate a query, “Select R.a from R,S where R.a = S.b.” A conventional join would use a nested loop algorithm, with an indexed scan in the inner loop. With a join index, the join can be computed by scanning the join index, thus minimizing random I/O.

There are a variety of ways of implementing a join index. One can list pairs (clustered on R or clustered on S), or in the case of an equijoin, associate R and S RIDs with attribute values. The example below shows the join index for R.a = S.b, organized as a list of pairs.
$$ \begin{array}{ccccc}\hfill \hfill & \hfill \mathrm{R}.\mathrm{a}\hfill & \hfill \hfill & \hfill \mathrm{S}.\mathrm{b}\hfill & \hfill \mathrm{JoinIndex}\hfill \\ {}\hfill \hfill & \hfill \hfill & \hfill \hfill & \hfill \hfill & \hfill \left(0,7\right)\hfill \\ {}\hfill \hfill & \hfill \hfill & \hfill 0\hfill & \hfill 8\hfill & \hfill \left(1,0\right)\hfill \\ {}\hfill 0\hfill & \hfill 12\hfill & \hfill 1\hfill & \hfill 2\hfill & \hfill \left(1,3\right)\hfill \\ {}\hfill 1\hfill & \hfill 8\hfill & \hfill 2\hfill & \hfill 3\hfill & \hfill \left(2,4\right)\hfill \\ {}\hfill 2\hfill & \hfill 14\hfill & \hfill 3\hfill & \hfill 8\hfill & \hfill \left(2,9\right)\hfill \\ {}\hfill 3\hfill & \hfill 7\hfill & \hfill 4\hfill & \hfill 14\hfill & \hfill \left(2,9\right)\hfill \\ {}\hfill 4\hfill & \hfill 8\hfill & \hfill 5\hfill & \hfill 9\hfill & \hfill \left(4,0\right)\hfill \\ {}\hfill 5\hfill & \hfill 12\hfill & \hfill 6\hfill & \hfill 5\hfill & \hfill \left(4,3\right)\hfill \\ {}\hfill 6\hfill & \hfill 1\hfill & \hfill 7\hfill & \hfill 12\hfill & \hfill \left(5,7\right)\hfill \\ {}\hfill 7\hfill & \hfill 14\hfill & \hfill 8\hfill & \hfill 11\hfill & \hfill \left(7,4\right)\hfill \\ {}\hfill \hfill & \hfill \hfill & \hfill 9\hfill & \hfill 14\hfill & \hfill \left(7,9\right)\hfill \end{array} $$


Recommended Reading

  1. 1.
    Li Z, Ross KA. Fast joins using join indices. VLDB J. 1999;8(1):1–24.CrossRefGoogle Scholar
  2. 2.
    Valduriez P. Join indices. ACM Trans Database Syst. 1987;12(2):218–46.CrossRefGoogle Scholar

Copyright information

© Springer Science+Business Media New York 2016

Authors and Affiliations

  1. 1.AT&T Labs – ResearchFlorham ParkUSA

Section editors and affiliations

  • Torben Bach Pedersen
    • 1
  • Stefano Rizzi
    • 2
  1. 1.Department of Computer ScienceAalborg UniversityAalborgDenmark
  2. 2.DISIUniv. of BolognaBolognaItaly