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.
Sort merge join
Show all comments by “Rixt” on any blog post by “Nico”
A Sort Merge join will do this operations:
- Filter comments by Rixt, filter posts by Nico,
- Sort all comments by blog id,
- sort all blogs by blog id
- Pull one from each list to find matches
Oracle Execution Plan
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.