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:
Provide an example of SQL Join Operations that require sorts in Oracle?

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

Improve Productivity through Oracle Tuning

Through the release of Oracle8, Oracle8i, and Oracle9i, Oracle has evolved into a database solution that sovles many problems. The challenge for IT professionals is to ensure that they are able to leverage Oracle's powerful features to improve the productivity of their organizations. One of the most effective ways to do this is through Oracle tuning. As a result, there is a large set of tuning parameters and techniques, all designed to improve the performance of your Oracle database. Oracle tuning is a complex subject and entire books have been written about the specifics of Oracle tuning. However, there are some general goals that every Oracle DBA should follow in order to improve the performance of their systems.
A DBA should focus on the following Oracle topics:
  1. External tuning: We must remember that Oracle does not run in a vacuum. Here we will look at tuning the Oracle server for high performance.
  2. Row re-sequencing to reduce disk I/O: We must understand how reducing I/O is the most important goal of Oracle tuning.
  1. Oracle SQL tuning: Oracle SQL tuning is one of the most important areas of Oracle tuning, and it is not uncommon to dramatically improve the performance of an SQL statement by using a few simple SQL tuning rules.
  2. Tuning Oracle sorting: Sorting is a small but very important component of Oracle performance.
  3. Tuning Oracle contention: The setting for table and index parameters has a huge impact on UPDATE and INSERT performance.
We always start by tuning the Oracle external environment. No amount of Oracle tuning is going to help if the server has a shortage of RAM or CPU resources.

Oracle Query that invokes Sort

Query Sort 
SQL>explain plan for 

select /*+  ordered */
  e.deptno,
  d.deptno
from
  emp e,
  dept d
where
  e.deptno = d.deptno
order by 
  e.deptno, d.deptno 
  
SELECT STATEMENT [CHOOSE] Cost =17
  MERGE JOIN
    SORT JOIN 
      TABLE ACCESS FULL EMP [ANALYZED]
    SORT
      TABLE ACCESS FULL DEPT [ANALYZED]

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


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.