Merge joins are not used:
For joins using <, >, <=, >=, or != on the join columns.
For outer joins, that is, queries using *= or =*, and left join and right join.
For queries that include a text or image column or Java object columns in the select list or in a where clause.
For subqueries that are not flattened or materialized in parallel queries.
For multitable updates and deletes, such as:
update R set a = 5
from R, S, T
where ...
For joins to perform referential integrity checks for insert, update, and delete commands. These joins are generated internally to check for the existence of the column values. They usually involve joins that return a single value from the referenced table. Often, these joins are supported by indexes. There would be no benefit from using a merge join for constraint checks.
When the number of bytes in a row for a worktable would exceed the page-size limit (1960 bytes of user data) or the limit on the number of columns (1024). If the select list and required join columns for a join would create a worktable that exceeds either of these limits, the optimizer does not consider performing a merge join at that point in the query plan.
When the use of worktables for a merge join would require more than the maximum allowable number of worktables for a query (14).
There are some limits on where merge joins can be used in the join order:
Merge joins can be performed only before an existence join. Some distinct queries are turned into existence joins, and merge joins are not used for these.
Full-merge joins and left-merge joins can be performed only on the outermost tables in the join order.
