The use of a sort merge join in Oracle SQL is quite common, especially in cases where there are missing join predicate against one of the tables or a missing index.
In a sort merge join, Oracle must perform full scans on the target tables, sort the keys and join the rows together.
It's important not to confuse a merge join with a merge join cartesian, which is a special case which is usually avoided.
The Oracle docs note these hints for merge joins:
- Sort merge join - Force a sort merge join (use_merge) The use_merge hint forces a sort merge join.
- Merge anti join (merge_aj) - Transforms a NOT IN subquery into a merge anti-join.
- Merge semi-join (merge_sj) - The merge_sj hint is placed into an EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
- Turn off sort merge join (_sortmerge_inequality_join_off) - This hidden parameter will disable a sort merge join in cases where the predicate is an inequality.
