For non-equi joins, we use only NESTED LOOP JOIN. But for your use case, if you are only concerned with equivalence of the first 7 letters in the string 'full_id'. Then you can convert it into an equality predicate by doing something like : strleft(t1.full_id, 7) = strleft(t2.full_id, 7) So the plan for the new query would result in a hash join, which can be faster for your case. select count(*) from test_like t1 left join test_like t2 on strleft(t1.full_id, 7) = strleft(t2.full_id, 7) +------------------------------------------------------------------------------------+ |
| PLAN-ROOT SINK |
| | |
| 06:AGGREGATE [FINALIZE] |
| | output: count:merge(*) |
| | row-size=8B cardinality=1 |
| | |
| 05:EXCHANGE [UNPARTITIONED] |
| | |
| 03:AGGREGATE |
| | output: count(*) |
| | row-size=8B cardinality=1 |
| | |
| 02:HASH JOIN [LEFT OUTER JOIN, BROADCAST] |
| | hash predicates: strleft(t1.full_id, 7) = strleft(t2.full_id, 7) |
| | row-size=30B cardinality=unavailable |
| | |
| |--04:EXCHANGE [BROADCAST] |
| | | |
| | 01:SCAN KUDU [bik.test_like t2] |
| | row-size=15B cardinality=unavailable |
| | |
| 00:SCAN KUDU [bik.test_like t1] |
| row-size=15B cardinality=unavailable |
+------------------------------------------------------------------------------------+
... View more
