Tips for nested loop join
A nested loop join is often used if one or more of these conditions exist:
- The outer table is small.
- Selection predicates significantly reduce the
number of qualifying rows in the outer table.
- An index exists on the join columns and selection
predicate columns of the inner table.
- The number of data pages accessed in the inner
table is small.
- A join predicate does not exist or no equi-join
predicate exists.
The following conditions are needed for optimal nested loop join performance:
- The inner join column, or columns, match the
left-most key columns of an index. That is, an existing index can be used to perform the join.
- Local predicates exist on the inner table. This
reduces the join fan-out.
- The outer table is accessed through an index in the
inner table join order (the tables are clustered alike).
For more information, see Database Performance and Query Optimization in the Information Center
.