When data are guaranteed to be in the right order,
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.