MSJOIN (Merge scan join) node

This node represents a merge join.

Node 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 can 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.

Performance suggestions:
  • Use local predicates (that is, predicates that reference one table) to reduce the number of rows to be joined.
  • If statistics are not current, update them using the RUNSTATS command.

Feedback