Re: Group By Questions..

As Connor replied, the results will not be different, although they may be delivered in a different order.

I do not know the performance implications for the Oracle RDBMS server.

I have tried the experiment on the Oracle RDB server (formerly DEC RDB) and,
depending on the indexing situation, the execution plan can be dramatically different.
In particular, if there is a single multicolumn index on table AAA let's say B,C,D
then a group by B,C,D will walk that index, while group by C,B,D will sort the table.

The difference in execution times can easily be ten to one, for large tables.

Given the similarities between the Oracle 8 Cost based optimizer and the optimizer
in Oracle RDB, I would expect similar diferences, but I haven't made the test.

Jimmy wrote:
>
> Hello all,
>
> Assume I have a table with column A, B, C and D.
>
> I wonder the following SQL statements will produce the same results:
>
> i) select B, C, D, avg(A) from AAA group by B, C, D;
> ii) select B, C, D, avg(A) from AAA group by B, D, C;
> iii) select B, C, D, avg(A) from AAA group by C, B, D;
> ....
>
> i.e. does the order of group by clause affect the final result (or
> performance, if any) of the SQL statement? Anyone knows white papers or
> documents discussing this issue?
>
> Thanks,
> Jimmy
Received on Sat Oct 02 1999 - 08:07:00 CDT