The example query:
SELECT A, B, X, Y 
FROM OUTER, INNER
WHERE A=10 and X=B;
The outer table contains column A and column B as shown in the following table.
Table 1. OUTER table (indexed) contents
A B
10 1
10 1
10 2
10 3
10 6
The inner table contains column A and column B as shown in the following table.
Table 2. INNER table (indexed) contents
X Y
1 Davis
2 Jones
2 Smith
3 Brown
5 Blake
7 Stone
9 Meyer
Db2® performs the following steps to complete the hybrid join:
  1. Db2 scans the outer table.
  2. Db2 joins the outer table with RIDs from the index on the inner table. The index of the inner table is scanned for every row of the outer table. The result is the phase 1 intermediate table, which includes data from the OUTER table matched to RIDs from the INNER table as shown in the following table.
    Table 3. Intermediate table (phase 1) contents
    OUTER
    data
    OUTER
    data
    INNER
    RIDs
    10 1 P5
    10 1 P5
    10 2 P2
    10 2 P7
    10 3 P4
  3. Db2 sorts the data in the outer table and the RIDs, creating a sorted RID list and the phase 2 intermediate table.
    Table 4. Intermediate table (phase 2) contents
    OUTER
    data
    OUTER
    data
    INNER
    RIDs
    10 2 P2
    10 3 P4
    10 1 P5
    10 1 P5
    10 2 P7
    The sorted RID list includes:
    • P2
    • P4
    • P5
    • P7
  4. Db2 retrieves the data from the inner table, using list prefetch.
  5. Db2 concatenates the data from the inner table and the phase 2 intermediate table to create the final composite table.
    The final composite table contains columns A, B, X and Y as shown in the following table.
    Table 5. Final composite table contents
    A B X Y
    10 2 2 Jones
    10 3 3 Brown
    10 1 1 Davis
    10 1 1 Davis
    10 2 2 Jones