Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 >= TO_DATE('01-Dec-2010')) AND (date_range_end <= 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|   |   |      |
-------------------------------------------------------------------------------------------------------------------------------------
share|improve this question
1  
do you have a tkprof/xplan showing this behaviour? –  Niall Litchfield Feb 16 '12 at 16:27
    
@NiallLitchfield I have the xplan (updated), I can't get tkprof at the moment. –  MK01 Feb 16 '12 at 16:38
    
How many rows does the query return? –  ypercube Feb 17 '12 at 0:35
    
@ypercube the table above contains 2 columns: E-Rows and A-Rows. E-Rows stands for "Estimated Rows", A stands for "Actual". So the query returns 12 rows. –  MK01 Feb 17 '12 at 1:03
    
Is this always true: date_range_start <= date_range_end ? –  ypercube Feb 17 '12 at 1:10

2 Answers 2

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 >= TO_DATE('01-Dec-2010')) AND (date_range_end <= TO_DATE('30-Nov-2011')) 
/

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

share|improve this answer

If userId cannot be NULL, change COUNT(userId) to COUNT(*).

If date_range_start <= date_range_end is true for all rows, then you could slightly change the query to:

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

If you have an index on date_range_start, the change will help narrowing the search.

I would also think that an index on (date_range_start, date_range_end, age) would be most helpful for this query but Oracle behaves so much different than MySQL that I may be totally wrong.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.