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.
| 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.
| 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:
- Db2 scans the outer table.
- 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
dataOUTER
dataINNER
RIDs10 1 P5 10 1 P5 10 2 P2 10 2 P7 10 3 P4 - 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
dataOUTER
dataINNER
RIDs10 2 P2 10 3 P4 10 1 P5 10 1 P5 10 2 P7 The sorted RID list includes:- P2
- P4
- P5
- P7
- Db2 retrieves the data from the inner table, using list prefetch.
- 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