SQL and Access Paths › Access Paths › Merge Scan Join

Merge Scan Join

Merge scan join is used as the table access method when DB2 detects one of the following facts:

• Many rows of the inner and outer table qualify for the join.
• The tables have no indexes on the matching columns of the join.

Note: A merge scan join is indicated with a value of 2 in the METHOD column of the PLAN_TABLE.

In a merge scan join, DB2 scans both tables in the order of the join columns. If no efficient indexes are providing the join order for the join columns, DB2 sorts the outer table, the inner table, or both tables. The inner table is placed into a work file. If the outer table has to be sorted, it is placed into a work file. DB2 then reads both tables and join the rows together using a match/merge process.