Databases Reference
In-Depth Information
Group 6
Hash Join 5 & 1
Join 3 & 4
Join 1 & 5
Join 5 & 1 Nested Loops 5 & 1
Group 5
Join 4 & 2 Nested Loops 2 & 4
Merge Join 4 & 2
Join 2 & 4
Clustered Index
Scan
Scan
Customer
Group 4
Group 3
Join 1 & 2
Join 2 & 1 Nested Loops 1 & 2
Clustered Index
Scan
Scan
Individual
Group 2
Clustered Index
Scan
Scan
Contact
Group 1
Table 5-10:
Memo structure with physical operators.
After the cost is estimated for each physical operator, the Query Optimizer will look for
the cheapest way to assemble a plan using the alternatives available. In our example, it
would select the plan operators shaded in gray in Table 5-10. As a result, the execution
plan selected by SQL Server is the plan shown in Figure 5-5, and you should notice that
the Query Optimizer did not select the join order that was explicitly requested in the
query text and shown in Group 1's tree representation. Instead, the Query Optimizer
found a better join order with a lower cost.
Figure 5-5:
Selected execution plan.
