Join Order Optimization
Join order optimization selects the best order for multi-table joins; with N! orders for N tables, it requires pruned search.
Purpose
To understand performance differences across join orders and avoid Cartesian products and bad orderings.
Mechanism
Dynamic programming (System-R) enumerates all join orders for the optimum in O(3^N). Large joins use greedy or heuristic pruning. Left-deep trees restrict the search space. Join order hugely affects intermediate result sizes.
Use cases
- Multi-table join tuning
- Execution plan analysis
- Optimizer internals