|
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.
|
