Oracle Database - SORT JOIN (or SORT MERGE JOIN)

SORT JOIN sorts a set of records that is to be used in a MERGE JOIN operation.

The example from the MERGE JOIN topic will be used again. All of the tables used in this example are fully indexed, so the following example deliberately disables the indexes by adding 0 to the numeric keys during the join, in order to force a merge join to occur.

SELECT COMPANY.Name 
FROM COMPANY, SALES
WHERE COMPANY.Company_ID+0 = SALES.Company_ID+0
AND SALES.Period_ID =3
AND SALES.Sales_Total>1000;

MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SALES
SORT JOIN
TABLE ACCESS FULL COMPANY

The Execution Plan shows that the COMPANY table and SALES table will be accessed using TABLE ACCESS FULL operations. Before the records from those tables are passed to the MERGE JOIN operation, they will first be processed by SORT JOIN operations that sort the records. The SORT JOIN output is used as input to the MERGE JOIN operation.