Access methods and costing for sort-merge joins

There are four possible execution methods for merge joins:

Merge joins always operate on stored tables – either user tables or worktables created for the merge join. When a worktable is required for a merge join, it is sorted into order on the join key, then the merge step is performed. The costing for any merge joins that involve sorting includes the estimated I/O cost of creating and sorting a worktable. For full-merge joins, the only cost involved is scanning the tables.

Figure 23-2 provides diagrams of the merge join types.

Figure 23-2: Merge join types