Oracle SQL Tuning: Avoiding Sort Options

codeling 1264 - 5430
@2016-02-15 12:45:25

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.

codeling 1264 - 5430
@2016-02-15 12:56:44

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)
  (select distinct employee_id
   from job_history
   where employee_id < ' 00166') jh (employee_id);

codeling 1264 - 5430
@2016-02-16 11:26:55

SORT opertion and Its Values Produced by EXPLAIN PLAN



Retrieval of a single row that is the result of applying a group function to a group of selected rows.



Operation sorting a set of rows to eliminate duplicates.



Operation sorting a set of rows into groups for a query with a GROUP BY clause.



Operation sorting a set of rows into groups for a query with a GROUP BY clause. The PIVOT option indicates a pivot-specific optimization for the SORT GROUP BY operator.



Operation sorting a set of rows before a merge-join.



Operation sorting a set of rows for a query with an ORDER BY clause.

Users browsing this topic