NLJOIN

NLJOIN

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.

For more information, see the section on join concepts in the Administration Guide.

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.

  For more guidelines about indexes, see Creating appropriate indexes.

• If statistics are not current, update them using the runstats command.

Related information:

• Star Joins.