About the optimizer's choice of sort avoidance

Some SQL statements require that data be ordered, including those with ORDER BY, GROUP BY, and DISTINCT. MIN() and MAX() aggregates also require ordering of data.

Derby can sometimes avoid sorting steps for:

• Statements with ORDER BY

  See Cost-based ORDER BY sort avoidance.

Derby can also perform the following optimizations, but they are not based on cost:

• Sort avoidance for DISTINCT and GROUP BYs

  See Non-cost-based sort avoidance (tuple filtering).

• Statements with a MIN() aggregate

  See The MIN() and MAX() optimizations.

• Cost-based ORDER BY sort avoidance
  Usually, sorting requires an extra step to put the data into the right order. This extra step can be avoided for data that are already in the right order.