Join Order Optimization
Joins are associative, so an n-table query has an exponentially large space of orders whose intermediate result sizes differ by orders of magnitude; the optimizer searches this space (dynamic programming for small n, heuristics/genetic search beyond a threshold) using cardinality estimates. Estimation errors compound multiplicatively through each join, so deep join trees are where plans go wrong first.
This Concept is waiting for its first lesson!
Joins are associative, so an n-table query has an exponentially large space of orders whose intermediate result sizes differ by orders of magnitude; the optimizer searches this space (dynamic programming for small n, heuristics/genetic search beyond a threshold) using cardinality estimates. Estimation errors compound multiplicatively through each join, so deep join trees are where plans go wrong first.
Are you a teacher? Sign in to start contributing.
Sign In