The min/max cost-based optimization is designed to exploit an existing index to compute efficiently the result of a simple aggregation query involving the MAX or MIN aggregate functions. The goal of this optimization is to be able to compute the result by retrieving only a few rows from the index. To be a candidate for this optimization, the query:

Example

To illustrate this optimization, assume that an index called prod_qty (ShipDate ASC, Quantity ASC) exists on the SalesOrderItems table. Then the query

SELECT MIN( Quantity )
 FROM SalesOrderItems
 WHERE ShipDate = '2000-03-25';

is rewritten internally as

SELECT MAX( Quantity )
 FROM ( SELECT FIRST Quantity
        FROM SalesOrderItems
        WHERE ShipDate = '2000-03-25' 
            AND Quantity IS NOT NULL
 ORDER BY ShipDate ASC, Quantity ASC ) AS s(Quantity);

The NULL_VALUE_ELIMINATED warning may not be generated for aggregate queries when this optimization is applied.

The execution plan (short form) for the rewritten query is:

GrByS[ RL[ SalesOrderItems<prod_qty> ] ]