SQL and Access Paths › Access Paths › Hybrid Join

Hybrid Join

When DB2 detects that the inner table access is a non-clustering index or a clustering index in which the cluster ratio is low, the hybrid join access method is used.

Note: The hybrid join is indicated with a value of 4 in the METHOD column of the PLAN_TABLE.

In a hybrid join, DB2 scans the outer table using a table space scan or an index. DB2 then joins the qualifying rows of the outer table with the RIDs from the matching index entries. DB2 creates a RID list for all the qualifying RIDs of the inner table. DB2 sorts the outer table and RIDs creating a sorted RID list and an intermediate table. DB2 then accesses the inner table using the list prefetch and joins the inner table to the intermediate table.

Hybrid join can outperform a nested loop join when the inner table access is a non-clustering index or clustering index for a disorganized table. The hybrid join is better if the equivalent nested loop join would be accessing the inner table in a random fashion and accessing the same pages multiple times. Hybrid join takes advantage of the list prefetch processing on the inner table to read all the data pages only once in a sequential or skip sequential manner. The hybrid join is also better when the query processes more than a tiny amount of data or less than a very large amount of data.

Because the list prefetch is used, the hybrid join can experience RID list failures. RID list failures result in one of the following actions:

• A table space scan of the inner table on initial access to the inner table.
• A restart of the list prefetch processing on subsequent accesses.

If you are experiencing RID list failures in a hybrid join, try to encourage DB2 to use a nested loop join.