Using expressions on join predicates
can limit the join method used.
A hash join method will be considered
for join predicates with an expression, as long as none of the following
types of functions are used:
- A function that reads or modifies SQL data
- A function that is non-deterministic
- A function that has an external action
- A function that uses a scratchpad
If columns from both operands of the associated join are used
on the same side of the join condition, hash joins are also considered,
but cannot be chosen in most cases.
If a hash join cannot be used, then a potentially slower nested
loop join will be used instead, and in these cases the cardinality
estimate might be inaccurate.
An example of a join with an expression
that is considered for a hash join:
WHERE UPPER(CUST.LASTNAME) = TRANS.NAME
An example of a join with expression
that would not benefit from a hash join, but would use a nested loop
join instead:
WHERE RAND() > 0.5
DB2 Version 10.1 for Linux, UNIX, and Windows