Sorting is an expensive process.
Derby tries to eliminate
unnecessary sorting steps where possible.
DISTINCT elimination based on a uniqueness condition
A DISTINCT (and the corresponding sort) can be eliminated from a query if a uniqueness condition exists that ensures that no duplicate values will be returned.
Combining ORDER BY and DISTINCT
Without a transformation, a statement that contains both DISTINCT and ORDER BY would require two separate sorting steps-one to satisfy DISTINCT and one to satisfy ORDER BY. (Currently, Derby uses sorting to evaluate DISTINCT. There are, in theory, other ways to accomplish this.)
Combining ORDER BY and UNION
Without a transformation, a statement that contains both ORDER BY and UNION would require two separate sorting steps-one to satisfy ORDER BY and one to satisfy UNION.