Oracle Database - SORT JOIN (or SORT MERGE JOIN)

Card Puncher Data Processing

About

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 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.





Discover More
Card Puncher Data Processing
Oracle Database - How to detect read from the temporary tablespace ?

How to detect read from the temporary tablespace ? Step followed : disable automatic PGA management by the server set the hash area size. and play with the sort area size In this example, the...
Card Puncher Data Processing
Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Joins are data operation / method / algorithm in order to match data from more than one table. They all require sorting and then matching aspects of a dataset. There are only three join mechanisms used...
Card Puncher Data Processing
Oracle Database - MERGE JOIN operation

MERGE JOIN joins tables by merging sorted lists of records from each tableon the join column. It is effective for large batch operations, but may be ineffective for joins used by transaction-processing...
Parallel Linearly Scalable Operation
Oracle Database - Parallel Operations

The fundamental difference and advantage of Oracle's capabilities, however, is that parallel data access and therefore the necessary data redistribution are not constrained by any given hardware architecture...
Card Puncher Data Processing
Oracle Database - SORT_AREA_SIZE parameter

SORT AREA SIZE is a parameter which set the sort area size of the pga memory. It will have no effect if the WORKAREA_SIZE_POLICY is set to auto. The sort area size control how much memory will be dynamically...



Share this page:
Follow us:
Task Runner