Binary join information
Output from the db2expln command can contain information about joins in an explained statement.
Hash Join Merge Join Nested Loop Join
Left Outer Hash Join Left Outer Merge Join Left Outer Nested Loop Join
In the case of a merge or nested loop join, the outer table of the join is the table that was referenced in the previous access statement (shown in the output). The inner table of the join is the table that was referenced in the access statement that is contained within the scope of the join statement. In the case of a hash join, the access statements are reversed: the outer table is contained within the scope of the join, and the inner table appears before the join.
In some circumstances, a join simply needs to determine whether any row in the inner table matches the current row in the outer table.
Early Out: Single Match Per Outer Row
It is possible to apply predicates after a join has completed. This statement displays the number of predicates being applied.
Residual Predicate(s) | #Predicates = n
The hash table is built from the inner table. This statement displays if the building of the hash table was pushed down into a predicate during access to the inner table.
Process Hash Table For Join
While accessing the outer table, a probe table can be built to improve the performance of the join. This statement displays if a probe table was built during access to the outer table.
Process Probe Table For Hash Join
This statement displays the estimated number of bytes that are needed to build the hash table.
Estimated Build Size: n
This statement displays the estimated number of bytes that are needed to build the probe table.
Estimated Probe Size: n
This statement indicates that the inner table of the join is the result of another series of operations. This is also referred to as a composite inner.
Access ..... W Join | Access ..... X Join | Access ..... Y Join | Access ..... Z
- Take a qualifying row from table W.
- Join a row from W with the next row from table X and call the result P1 (for partial join result number 1).
- Join P1 with the next row from table Y to create P2.
- Join P2 with the next row from table Z to create one complete result row.
- If there are more rows in Z, go to step 4.
- If there are more rows in Y, go to step 3.
- If there are more rows in X, go to step 2.
- If there are more rows in W, go to step 1.