ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

Optimization for minimum or maximum functions

The min/max rewrite optimization is designed to exploit an existing index to efficiently compute 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 with a single-row lookup using the index. To be a candidate for this optimization, the query:

• must not contain a GROUP BY clause

• must be over a single table

• cannot contain anything other than conjunctive equality conditions in the WHERE clause

• 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 prod_qty on (prod_id ASC, quantity ASC) exists on the sales_order_items table. Then the query

SELECT MIN( quantity )
FROM sales_order_items
Where prod_id = 300

is rewritten internally as

SELECT MIN( quantity )
FROM ( SELECT FIRST quantity
       FROM sales_order_items
       WHERE prod_id = 300 and quantity IS NOT NULL
       ORDER BY prod_id ASC, quantity ASC ) as s(quantity)

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

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

GrByS[ RL[ sales_order_items<prod_qty> ] ]