Re: help me win this "group by" argument
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 24 Mar 2004 13:26:09 -0000
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(*)
-- no line about exceptions
select object_type, sum(*)
Total number of exceptions fired 91
select object_type, avg(*)
Total number of exceptions fired 69
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
----- Original Message -----
From: <babette.turnerunderwood@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 23, 2004 8:32 PM
Subject: help me win this "group by" argument
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
