Oracle aggregate function performance Can Oracle be smart about aggregate functions, such as MIN(), MAX(), and (AVG)?  My testing shows that it seems to be surprisingly stupid.

I have the following query:

SELECT COUNT(userId), AVG(age), 
 STDDEV(age), MIN(age), MAX(age), 
 date_range_start, date_range_end
FROM users
WHERE
(date_range_start &gt;= TO_DATE('01-Dec-2010')) AND (date_range_end &lt;= TO_DATE('30-Nov-2011')) 
GROUP BY date_range_start, date_range_end;


It takes 27 seconds.
Now I remove the STDDEV, MIN and MAX aggregations the same query takes only 12 seconds.
OK, I can see STDDEV slowing things down as it requires 2 passes.
So I try AVG + MIN and MAX -- I get 21s.  

How is this even possible?  How can adding calculation of min and max to the calculation of AVG slow things down by the factor of 2 almost?  Considering that out of the 12 seconds that it takes with AVG only 10 are spent on the full table scan?  So adding min/max calculation changes the group by step from 2 seconds to 10?  

The explain plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     12 |00:00:24.61 |     369K|    369K|   |   |      |
|   1 |  HASH GROUP BY     |              |      1 |     12 |     12 |00:00:24.61 |     369K|    369K|   762K|   762K|   11M (0)|
|*  2 |   TABLE ACCESS FULL| USER         |      1 |     29M|     29M|00:00:09.34 |     369K|    369K|   |   |      |
-------------------------------------------------------------------------------------------------------------------------------------
Use Oracle analytics:

SELECT distinct
  COUNT(userId) over (partition by date_range_start, date_range_end)
, AVG(age)      over (partition by date_range_start, date_range_end)
, STDDEV(age)   over (partition by date_range_start, date_range_end)
, MIN(age)      over (partition by date_range_start, date_range_end)
, MAX(age)      over (partition by date_range_start, date_range_end)
, date_range_start, date_range_end
FROM users
WHERE
(date_range_start &gt;= TO_DATE('01-Dec-2010')) AND (date_range_end &lt;= TO_DATE('30-Nov-2011')) 
/


It does the same but most of the times it is surprizingly faster.