Encyclopedia of Database Systems

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

Join Order

  • Jingren ZhouEmail author
Living reference work entry
DOI: https://doi.org/10.1007/978-1-4899-7993-3_871-2

Synonyms

Definition

A database query typically contains multiple joins. When joins (for example, inner joins) are commutative and/or associative, there can more than one evaluation order for joins. The join order has an enormous impact on the query cost. One of the main responsibilities of the query optimizer is to determine the optimal join order for query evaluation.

Key Points

Choosing a good join order is very important to achieve a good query performance. One important consideration for choosing an join order is to reduce the size of intermediate results as much as possible. For example, it is beneficial to first evaluate a join that returns the least result. Other considerations include join methods, data properties, and access methods, etc.

Depending on the choice of join orders, query plans can be of different shapes.
  • Left-Deep query plans use a base table as the inner table for each join.

  • Right-Deep query plans use a base table as the outer table for each join.

  • Bushy query plans use the intermediate result from other joins as join inputs.

Query optimizers typically use dynamic programming and heuristics to determine join orders.

Cross-References

Recommended Reading

  1. 1.
    Mishra P, Eich MH. Join processing in relational databases. ACM Comput Surv. 1992;24(1):63–113.CrossRefGoogle Scholar
  2. 2.
    Selinger PG, Astrahan MM, Chamberlin DD, Lorie RA, Price TG. Access path selection in a relational database management system. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 1979. p. 23–34.Google Scholar

Copyright information

© Springer Science+Business Media LLC 2017

Authors and Affiliations

  1. 1.Alibaba GroupHangzhouChina

Section editors and affiliations

  • Evaggelia Pitoura
    • 1
  1. 1.Department of Computer Science and EngineeringUniversity of IoanninaIoanninaGreece