NLJOIN operator

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.

A nested loop join is performed either:
  • 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.
Performance suggestions:
  • 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.