Oracle SQL Tuning: Avoiding Sort

By its nature, sort is CPU- and I/O-intensive, as it must reorder a set of rows by comparing values from every row.

Examining the Optimizer strategy is one way to see whether a sort is being performed. However, there are are some standard operators that will often cause a sort to occur. You should keep these in mind when tuning queries:

• DISTINCT will often cause sort to be used so that rows with the same values can be eliminated.
• GROUP BY and ORDER BY will order the set of data using sort if no suitable index is available. This is always true when these clauses use columns from different tables or when they contain expressions.
• The UNION operator, by default, returns only the distinct rows from the merged tables. If you know that the results are already unique, or if you can tolerate the duplicate rows, use the UNION ALL operator.
• Joins between tables with no suitable sorted index may require a sort so that the matching rows can be found and returned for the query.

The goal is to use DISTINCT only on those columns that need to be reduced to a unique subset. This will result in lower sort overhead (shorter records to sort
means less virtual memory and smaller temporary files). We also hope to encourage the Optimizer to use a sorted index to avoid the sort completely.

Applying DISTINCT to a minimal column list could be done using a view or a derived table, as shown in the example below:

 select employee_id,
     (select last_name
      from employee e
      where e.employee_id = jh.employee_id)
 from
  (select distinct employee_id
   from job_history
   where employee_id < ' 00166') jh (employee_id);