DB2 join methods

Join methods at a glance

Each PLAN_TABLE row is a step. The first table in a query block has METHOD = 0. Each later table is joined with METHOD 1, 2, or 4. A METHOD 3 row is a sort of the composite, not a new table. Independently, ACCESSTYPE on that row tells how that table was read (index, scan, and so on).

1
2
3
4
5
6
7
8
9
10
11
12
SELECT E.EMPNO, D.DEPTNAME
  FROM HR.EMPLOYEE E
  JOIN HR.DEPT D
    ON E.WORKDEPT = D.DEPTNO
 WHERE E.WORKDEPT = 'A00';
 
-- EXPLAIN, then:
SELECT PLANNO, METHOD, TNAME, ACCESSTYPE, MATCHCOLS,
       ACCESSNAME, SORTN_JOIN, SORTC_JOIN, JOIN_TYPE
  FROM PLAN_TABLE
 WHERE QUERYNO = 2001
 ORDER BY QBLOCKNO, PLANNO, MIXOPSEQ;

Join sequence

Join sequence is which table is accessed first, which is joined next, and so on. The optimizer enumerates (or heuristically searches) orders because A-then-B can cost far less than B-then-A. A small department table filtered to one row should usually be outer; a huge employee table should be inner with a matching index on WORKDEPT—not the other way around.

Read sequence from PLANNO within QBLOCKNO. Do not assume FROM-clause order. Views, subqueries, and query rewrite can add extra query blocks; DSN_STRUCT_TABLE helps map those blocks. Parent and child blocks have their own sequences.

After the first table, the “outer” side is the composite—everything joined so far. SORTC_* flags talk about that composite. SORTN_* flags talk about the new (inner) table on this step.

Join predicates

A join predicate relates two tables:

1
2
3
4
5
6
7
8
9
10
-- Explicit join predicate in ON
SELECT ...
  FROM HR.EMPLOYEE E
  INNER JOIN HR.DEPT D
    ON E.WORKDEPT = D.DEPTNO
 
-- Same relationship in WHERE (older style)
SELECT ...
  FROM HR.EMPLOYEE E, HR.DEPT D
 WHERE E.WORKDEPT = D.DEPTNO

• Equijoin (T1.C = T2.C) — the usual food for merge scan and hybrid join, and for nested-loop inner index matching
• Range join (T1.C BETWEEN T2.LOW AND T2.HIGH) — nested loop can still apply it; merge scan wants ordered equality-style merge keys
• Local predicates (E.WORKDEPT = 'A00') — filter one table; they change cardinality and thus which table should be outer
• ON versus WHERE on outer joins — a predicate in WHERE on the inner table can turn a LEFT JOIN into an inner join logically. Keep inner-only filters in ON when you mean outer join

IBM notes that hybrid and merge scan require join columns. Nested loop does not: if there is no join predicate, nested loop can still pair every outer row with inner rows (a Cartesian nest). That is occasionally intended and often a missing join predicate bug. Always look at METHOD 1 with no join predicate and a huge inner scan as a red flag.

Predicate transitive closure can add implied join or local predicates (if A = B and B = 5, then A = 5). That extra local predicate may enable matching index access on a table that was not written with that literal. DSN_PREDICAT_TABLE shows what the optimizer actually used.

Nested loop join (METHOD = 1)

In a nested loop join Db2 scans the composite (outer) once. For each qualifying outer row it searches the new (inner) table for matches and concatenates them. If no inner row matches, inner join drops the outer row; outer join keeps it with nulls as SQL defines.

The inner table is accessed once per qualifying outer row. Therefore nested loop is usually best when:

• The outer table is small or local predicates shrink it
• An efficient, preferably clustered index exists on the inner join columns, or Db2 can build a sparse index on the inner
• Few inner data pages are touched per probe
• There are no join columns (merge and hybrid cannot be used)

Nested loop is usually poor when join columns are out of sequence, the inner has no useful index, the index is poorly clustered, and the outer is large—each inner scan or random I/O repeats. Stage 1 and stage 2 predicates still apply during the join; a stage 2 inner predicate evaluated millions of times will show up as CPU, not as ACCESSTYPE R.

Db2 can sort the composite (SORTC_JOIN = Y) so inner index probes follow key order and data I/O becomes more sequential. That sorted nested loop is a hybrid of ideas: still METHOD 1, but with a sort to help clustering. Sparse indexes reduce the need to sort the outer into join order when hashing fits in memory.

1
2
3
4
5
6
7
8
9
-- Typical good nested loop: tiny outer, indexed inner
SELECT D.DEPTNAME, E.LASTNAME
  FROM HR.DEPT D
  JOIN HR.EMPLOYEE E
    ON E.WORKDEPT = D.DEPTNO
 WHERE D.DEPTNO = 'A00';
 
-- Hope: DEPT is METHOD 0, index on DEPTNO
-- EMPLOYEE is METHOD 1, matching index on WORKDEPT

Merge scan join (METHOD = 2)

In a merge scan join (sort-merge join) both the composite and the new table are in join-column order. Db2 scans them together like merging two sorted files: advance the side with the smaller key, and concatenate when keys match. Duplicates are handled according to inner versus outer join rules.

Order comes from:

• An index on the join columns (index scan already in order)
• A sort: SORTC_JOIN for the composite, SORTN_JOIN for the new table

Merge scan is a natural fit when both inputs are large, an equijoin is present, and neither side is a tiny outer with a perfect inner index. You pay sorts (CPU, work files) to avoid nested-loop inner probes. If both clustering indexes already match the join keys, merge can be almost “just two scans.” If both sides sort, watch work-file contention.

Merge scan cannot be used without join columns. Non-equality join predicates generally push you toward nested loop. Highly selective local predicates on one table may still make nested loop cheaper than sorting both large inputs—costing decides.

Hybrid join (METHOD = 4), briefly

Hybrid join sits between nested loop and list prefetch. The outer is scanned; inner RIDs are collected via an index, sorted, and the inner table is read with list prefetch. It needs join columns. Use it in your mental model when the inner index is useful for finding rows but not clustered for nested-loop I/O. RID pool failures can undermine it—see the prefetch page.

How access paths compose with joins

Join method and table access method are separate knobs:

• Nested loop + matching index on inner — classic OLTP
• Nested loop + tablespace scan on inner — usually a problem if the outer has many rows
• Merge scan + tablespace scan on both — possible for large unfiltered equijoins
• Merge scan + matching index-only on one side — the index provides order and columns
• Hybrid + list prefetch on inner — RID list I/O

Always read ACCESSTYPE and MATCHCOLS on each join step, not only METHOD on the second table.

Outer joins, stars, and more than two tables

Three-table joins are two steps: join T1 to T2, then join that composite to T3 (or another order). Each step has its own METHOD. A bad second step can dominate even if the first join is perfect.

LEFT/RIGHT/FULL OUTER JOIN set JOIN_TYPE and restrict reorderings: you cannot freely make the preserved table inner if that would drop unmatched rows. Star join (fact plus dimensions) has extra matching rules on the fact index; it is a specialized sequence, not a fourth everyday METHOD value for beginners.

Tuning checklist

1. Confirm join predicates exist and use the same data types/CCSID so they are indexable.
2. Check join sequence: is the smaller filtered set outer for nested loop?
3. For METHOD 1, confirm inner MATCHCOLS and clustering; consider a sparse-index or missing index if ACCESSTYPE is R.
4. For METHOD 2, check SORTN_JOIN / SORTC_JOIN and work-file use.
5. For METHOD 4, check RID pool and PREFETCH L.
6. Remember access path stability: a join method flip after RUNSTATS is a common regression.

Explain It Like I'm Five

Nested loop is looking up each classmate's phone number one by one in the phone book: fine for three friends, terrible for the whole school if the book is messy. Merge scan is sorting both the class list and the phone book by name and walking down the two lists with a finger on each. Hybrid join is writing every classmate's page number on scrap paper, sorting the page numbers, and fetching those pages in stacks. Join sequence is who you start with—the tiny class list or the giant phone book. Join predicates are the rule “match on last name.”

Exercises

1. EXPLAIN a two-table inner join. Identify METHOD 0 versus 1/2/4 and which table is first (PLANNO).
2. Add a local predicate that reduces one table to one row. See whether join sequence flips.
3. Find SORTC_JOIN or SORTN_JOIN = Y on a merge join and name which input was sorted.
4. Rewrite an old comma FROM with WHERE join predicates into INNER JOIN ... ON form without changing results.
5. Intentionally omit a join predicate on a sandbox and observe METHOD 1 with a huge composite. Estimate why that is dangerous in production.