Binary join information
Output from the db2expln command can contain information about joins in an explained statement.
Whenever a binary join is performed, one of the following statements is displayed:
Hash Join Merge Join Nested Loop Join
A left outer join is indicated by one of the following statements:
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 the case of a hash or merge join, the following additional statements might appear:
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
In the case of a hash join, the following additional statements might appear:
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
In the case of a nested loop join, the following statement might appear immediately after the join statement:
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.
If a join involves more than two tables, the explain steps should be read from top to bottom. For example, suppose the explain output has the following flow:
Access ..... W Join | Access ..... X Join | Access ..... Y Join | Access ..... Z
The steps of execution would be:
- 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.