SORT JOIN sorts a set of records on the join column.
It is used in combination with a a MERGE JOIN operation in order to perform a sort_merge_join.
Show all comments by “Rixt” on any blog post by “Nico”
A Sort Merge join will do this operations:
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.