Foundation1 / Development Guide / Core Server / Base Server Dev / Performance / This page

Unnecessary Sorting, Grouping and Distinct

Oracle may need to perform sorts under the following circumstances:

When an index is created
When an aggregation is requested via the GROUP BY or DISTINCT keywords
When the result set is sorted via the ORDER BY clause
When a join is performed but there are no suitable indexes (a sort merge join)
When the set operators UNION, INTERSECT or MINUS is used
When a correlated sub query is executed

The sorting in Oracle falls into two major categories: disk sorts and memory sorts. The sorting if possible should be performed in memory. Please note that memory is allocated for each session, so even comparatively small values can translate into large amounts of system memory. Whenever a sort is performed on disk instead of memory it will generally cause bad performance, especially on large sorts.

When using 'Explain Plan', the statistics section indicates the nature of the sorting 'Oracle Optimizer' decides for the particular operation.

Examples of sorting in 'Explain Plan' and their explanation:

Sorting	Description
SORT AGGREGATE	The group function SUM() operation
SORT GROUP BY	GROUP BY clause
SORT JOIN	A by-product of the MERGE JOIN operation
SORT ORDER BY	The ORDER BY clause
SORT UNIQUE	A DISTINCT clause or a by product of a UNION operation

Rules

Avoid "accidental" sorts. Sorting always affects performance negatively.

Different things can cause Oracle to perform a sort that you don't really require:

Unnecessary use of the DISTINCT clause: The DISTINCT clause will almost always require a sort to eliminate duplicate rows. Sometimes, the DISTINCT clause is necessary and unavoidable, but bear in mind that it does have a sort overhead, so use it only when necessary.
Using UNION instead of UNION ALL: The UNION operator sorts the result set to eliminate any rows that are duplicated within the sub queries. UNION ALL includes duplicate rows, and therefore does not require a sort. If you don't require that duplicate rows should be eliminated, then use UNION ALL.