| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: help me win this "group by" argument
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.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php 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 ?
Thanks
 |
 |