Why does the optimizer choose nested loops over merge joins here?
Why does it work like that and what should I do to get two Merge Joins?
With three table references (the minimum required), the query qualifies for the Transaction Processing (aka search 0) stage of cost-based optimization.
This stage is aimed at OLTP queries, which normally benefit from a navigational (index-based) strategy. Nested Loops Join is the main physical join type available (hash and merge are only considered if no valid nested loops plan can be found in this stage).
If this stage finds a low cost (good enough) plan, cost-based optimization stops there. This prevents spending more time in optimization that we can expect to save over the best solution found so far. If the cost exceeds a threshold, the optimizer moves on to the Quick Plan (search 1), parallel Quick Plan, and Full Optimization (search 2) phases.
The query with two table references did not qualify for Transaction Processing, and went straight into Quick Plan, where Merge and Hash joins are available.
See my Query Optimizer Deep Dive series for more information.
Without
inner merge join
hint.
If you absolutely must hint a physical join type, strongly prefer OPTION (MERGE JOIN)
. This allows the optimizer to still consider changing the join order.
Join hints like INNER MERGE JOIN
come with an implied OPTION (FORCE ORDER)
, which severely limits the optimizer's freedom, with consequences most people (including experts) do not appreciate.