(1) Operation : SORT UNIQUE
Cause : DISTINCT Clause, and IN sub-query where subquery is independent meand no co-relation with parenst query. If for IN subquery, there is an unique index on selected columns then SORT UNIQUE can be avoided.
(2) Operation : SORT AGGREGATE
Cause : Whenever u use group functions. It does not actually involve a sort. It is used when aggregates are being computed across the whole set of rows.
(3) Operation : SORT GROUP BY
Cause : when use “Group by” clause.
(4) Operation : SORT JOIN
Cause : SORT JOIN happens during a SORT MERGE JOIN, if the rows need to be sorted by the join key. Avoid this in OLTP with USE_NL(ALIAS1, ALIAS2) hint.
(5) Operation : SORT ORDER BY
Cause : SORT ORDER BY is required when the statement specifies an ORDER BY that cannot be satisfied by one of the indexes.