| 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
Could we infer that the operations occur after the read as the sort
shows 5000 rows? If it was computing the values on the fly, the sort
(group by performs an implicit sort) should only show the number of
distinct values of object_type.
Daniel
Jonathan Lewis wrote:
> 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_at_hrdc-drhc.gc.ca>
> To: <oracle-l_at_freelists.org>
> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Mar 24 2004 - 09:24:50 CST
 |
 |