sort join tips

Question:  How do understand the working of a SORT JOIN operation.  I have an execution plan with the following plan:

Answer: The Oracle use_merge hint requests a sort join (a type of sort merge join)  against the specified tables.  As the name implies, a sort join reads both tables (via a full-table scan) and then sorts the rows. The nest step is merging the sorted tables into a single result set:

The use_merge hint forces a sort merge operation. Make sure you run your sort join with parallel query since you are doing full-table scans.

The sort merge operation is often used in conjunction with parallel query because a sort merge join always performs full-table scans against the tables. Sort merge joins are generally best for queries that produce very large result sets such as daily reports and table detail summary queries, or tables that do not possess indexes on the join keys.

Here we see a simple query that has been formed to perform a sort join (use_merge) using parallel query against both tables.

select /*+ use_merge(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename,
   hiredate,
   b.comm
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

Here is the output of the execution plan for this query. Note the full-table scans and the sort merge operation:

OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
5
MERGE JOIN
1
PARALLEL_TO_SERIAL
SORT JOIN 1
PARALLEL_COMBINED_WITH_PARENT
TABLE ACCESS
FULL EMP 1
PARALLEL_TO_PARALLEL

SORT JOIN 2
PARALLEL_COMBINED_WITH_PARENT
TABLE ACCESS
FULL BONUS 1
PARALLEL_TO_PARALLEL

It is important to note that a sort merge join does not use indexes to join the tables. In most cases, index access is faster, but a sort merge join may be appropriate for a large tale join without a where clause, or in queries that do not have available indexes to join the tables.