Aggregate Clause Processing

When all aggregate functions in a query are on the same indexed field, the system will determine the result from the index rather than by using the slower process of accessing the base table. For example, given the following query, the server does not need to look at the table file if an index exists on PAYRATE; the index alone is sufficient to give the result.

SELECT MAX(payrate), MIN(payrate), AVG(payrate) 
    FROM employee

Likewise, any COUNT aggregate using a WHERE clause that can be resolved via an index will not access the base table. For example, if an index exists on columns A and B in TABLE1 and the following query is issued:

SELECT COUNT(*) FROM table1 
    WHERE a=2 AND b=3;

the information will be available via the index. The base table (TABLE1) is not accessed.