Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: help me win this "group by" argument

Re: help me win this "group by" argument

From: Jonathan Lewis <>
Date: Wed, 24 Mar 2004 13:26:09 -0000
Message-ID: <011b01c411a3$9396ede0$7102a8c0@Primary>

Create a table with 5,000 rows e.g.

    create table t1 as
    select object_type, rownum n from all_objects     where rownum <= 5000;

select count(*)
select sum(n)
select avg(n)

Execution path is sort (aggregate)
sorts (rows) statistic is zero.

select object_type, count(*)
select object_type, sum(*)
select object_type, avg(*)

Execution path is sort (group by)
sorts (rows) statistic is 5,000.

I don't think you can infer the exact
mechanism used - but I think it's
a fairly safe bet that Oracle really
does sort the data based on the group
by, then walk the sorted list. It seems a fairly safe bet, then, that avg() is
simply what happens at a break point
in the list, and is sum(n) / count(n)

One oddity shows up in the 10032 trace:

object_type, count(*)

So there does seem to be some difference in treatment between the different operations.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland June 2004 UK - Optimising Oracle Seminar

Two co-workers and I were discussing the mechanism that Oracle uses to do group by and whether sorts were required or not.

My understanding is the intermediate result set is sorted and whether the source data has be sorted or not depends on the type of group by function being applied. If it is a SUM or a COUNT, Oracle just increments the values. But if it is an average then Oracle sorts the original data into the group by values and then does the calculations.

Co-worker one says that Oracle always just increments the counts and for a AVG it will just be incrementing the SUM and COUNT and then use those for AVG.

Co-worker two says Oracle always sorts the data by the group by value and then applies the function..

So who's right ?


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Wed Mar 24 2004 - 07:22:59 CST

Original text of this message