Re: Group By Questions..

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

The result in terms of data will not be different...but it is possible that you will get the rows back in a different order (this would be negated by also specifying an order-by clause).

Performance would be dependent on two things:

1. the sort algorithm being used by Oracle (which without proving it I belive would be only small differences between the different permutations)
2. the intelligence of the optimizer with respect to the indexing of the grouping columns. If some/all of the grouping columns were indexed, then I think the optimizer would have a better chance at recoginising indexability if you grouped in index order (and thus gave Oracle the chance at avoiding the sort).

Cheers
Connor Received on Sat Oct 02 1999 - 04:48:17 CDT