Avoiding join predicates on expressions
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 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