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
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> ] ]
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|