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:

• The MIN() or MAX() is the only entry in the SELECT list.
• The MIN() or MAX() is on a simple column reference, not on an expression.
• For MAX(), there must not be a WHERE clause.
• For MIN():
  • The referenced table is the outermost table in the optimizer's chosen join order for the query block.
  • The optimizer chose an index containing the referenced column as the access path.
  • The referenced column is the first key column in that index OR the referenced column is a key column in that index and equality predicates exist on all key columns prior to the simple column reference in that index.

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.