Cost-based optimization with MIN and MAX functions

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:

must not contain a GROUP BY clause
must be over a single table
must contain only a single aggregate function (MAX or MIN) in the query's SELECT-list

Example

To illustrate this optimization, assume that an index called product_quantity (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<product_quantity> ] ]

Discuss this page in DocCommentXchange.