The MIN() and MAX() optimizations

The optimizer knows that it can avoid iterating through all the source rows in a result to compute a MIN() or MAX() aggregate when data are already in the right order. When data are guaranteed to be in the right order, Derby can go immediately to the smallest (minimum) or largest (maximum) row.

The following conditions must be true:
For example, the optimizer can use this optimization for the following queries (if the optimizer uses the appropriate indexes as the access paths):
-- index on orig_airport
SELECT MIN(orig_airport)
FROM Flights
-- index on orig_airport
SELECT MAX(orig_airport)
FROM Flights
-- index on orig_airport
SELECT miles 
FROM Flights 
WHERE orig_airport = (SELECT MIN(orig_airport)
FROM Flights)
-- index on segment_number, flight_id
SELECT MIN(segment_number) 
FROM Flights 
WHERE flight_id = 'AA1111' 
SELECT * 
FROM Flights 
WHERE segment_number = (SELECT MIN(segment_number) 
FROM Flights 
WHERE flight_id = 'AA1111')

The optimizer decides whether to implement the optimization after choosing the plan for the query. The optimizer does not take this optimization into account when costing the plan.

Related concepts
Non-cost-based sort avoidance (tuple filtering)