Oracle SQL sort merge join tips

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.

Here are my additional notes on the sort merge join: