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