This operator represents a nested loop join.
Operator name: NLJOIN
Represents: A nested loop join that scans (usually with an index scan) the inner table once for each row of the outer table.
A join is necessary whenever there is more than one table referenced in a FROM clause. A nested loop join does not require a join predicate, but generally performs better with one.
- By scanning through the inner table for each accessed row of the outer table.
- By performing an index lookup on the inner table for each accessed row of the outer table.
- A nested loop join is likely to be more efficient if there is
an index on the join-predicate columns of the inner table (the table
displayed to the right of the NLJOIN operator). Check to see if the
inner table is a TBSCAN rather than an IXSCAN. If it is, consider
adding an index on its join columns.
Another (less important) way to make the join more efficient is to create an index on the join columns of the outer table so that the outer table is ordered.
- If statistics are not current, update them using the RUNSTATS command.