Sorting Operations   «Prev  Next»
Lesson 4 SQL join operations that require Sorts
Objective Identify sorts in SQL Statements

SQL Join Operations that require Sorts

To be able to remove unnecessary Oracle sorting, you must first become familiar with poor SQL programming that can lead to sorting. As you may know, all SQL query processing can be divided into seven phases:

SQL query processing can be divided into seven phases

  1. Checks the syntax of the query
  2. Checks that all objects exist and are accessible
  3. Rewrites query as join on base tables as opposed to using views
  4. Rewrites query transforming some complex constructs into simpler ones where appropriate
  5. Determines the optimal access path for the query to take < Here Oracle chooses to sort
  6. Query evaluation plan generation
  7. Execute the SQL query

During this process, Oracle may make a decision that a final or intermediate result set requires re-sequencing and will invoke an Oracle sort.
Two most common sort operations invoke include, the ORDER BY sort, and the JOIN sort.

ORDER BY sort

The ORDER BY sort is most commonly seen in queries that have an ORDER BY clause and do not access the table using an index. In the example below, you will notice an unqualified select against the EMP table: 

select 
   * 
from 
   emp 
order by 
   empno;
SELECT STATEMENT Optimizer=CHOOSE 
   SORT (ORDER BY)
      TABLE ACCESS (FULL) OF 'EMP'


If the query is going to read the majority of the EMP rows then a full-table scan is fine. However, if the query is only reading a small portion of the rows (i.e. 
select * from emp where status = 3
) then you would tune the query by adding an index.

The JOIN sort

The JOIN sort is a very expensive operation and should be avoided whenever possible. To understand how a JOIN sort is created, consider the following example.
In this query, notice that the JOIN sort is caused by the lack of a WHERE clause to join the tables together, and Oracle must sort each result set separately and join based upon the sorted rows: 

SQL> explain plan for
select 
   emp.
   deptno,
   dept,
   deptno 
from 
   emp,
   dept
SELECT STATEMENT [CHOOSE] Cost=5 
   MERGE JOIN CARTESIAN 
      TABLE ACCESS FULL DEPT 
      SORT JOIN 
         TABLE ACCESS FULL EMP

Sort invoking Query
However, the lack of a WHERE clause is not the only time that a sort join can be invoked.
In the next lesson, you will learn to monitor your database for disk sorts.
Below is an example of invoking the sort join clause.
Merge Sort Join
  1. The syntax of this query requires that full-table scans are performed against each of the target tables.
  2. A sort operation is used to join the result sets together.
  3. Rows are produced by the EMP full-table scan and are then sorted.
  4. Rows from the DEPT full-table scan are then sorted by the same sort key as the EMP table.