MSJOIN

MSJOIN

Operator name: MSJOIN

Represents: A merge join for which the qualified rows from both outer and inner tables must be in join-predicate order. A merge join is also called a merge scan join or a sorted merge join.

A join is necessary whenever there is more than one table referenced in a FROM clause. A merge join is possible whenever there is a join predicate that equates columns from two different tables. It may also arise from a rewritten subquery.

A merge join requires ordered input on joining columns, since the tables are typically scanned only once. This ordered input is obtained by accessing an index or a sorted table.

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

Performance suggestions:

• Use local predicates (that is, predicates that reference one table) to reduce the number of rows to be joined.

  For guidelines about indexes, see Creating appropriate indexes.

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