web analytics
Oracle SQL Tuning: Avoiding Sort Options
codeling
Posted: Monday, February 15, 2016 12:45:25 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 771
Points: 3240

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.
Sponsor
Posted: Monday, February 15, 2016 12:45:25 PM
codeling
Posted: Monday, February 15, 2016 12:56:44 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 771
Points: 3240

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);
codeling
Posted: Tuesday, February 16, 2016 11:26:55 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 771
Points: 3240

SORT opertion and Its Values Produced by EXPLAIN PLAN

SORT

AGGREGATE

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

SORT

UNIQUE

Operation sorting a set of rows to eliminate duplicates.

SORT

GROUP BY

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

SORT

GROUP BY PIVOT

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.

SORT

JOIN

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

SORT

ORDER BY

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

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.