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.