Prev: The job of a relational DBMS (was: Sql request)
Next: Pivot table Query - Need help in building correct logic
From: Thomas Kellerer on 2 Dec 2009 03:55
Gene Wirchenko, 02.12.2009 04:10:
>
> Granted that that would be implementation-dependent, but it would
> be convenient for it to do it that way while grouping. (How else
> would it?)
At least Oracle and Postgres do some kind of hashing most of the time, which does not sort the results at all.

> It would be silly for the DBMS to sort internally and
> then the converter do it again.
Agreed. But I think any modern optimizer will combine these steps if that would be quicker

Regards
Thomas
From: ddf on 2 Dec 2009 09:53
On Dec 2, 2:13 am, Shakespeare <what...(a)xs4all.nl> wrote:
> Thomas Kellerer schreef:
>
> > Gene Wirchenko wrote on 01.12.2009 05:35:
> >>      For example, a group by in SQL forces the result to be sorted by
> >> the grouping unless otherwise overridden.
>
> > Never has been true. Group by does *not* sort the result. Not even in
> > Oracle 8 and and certainly not for any Oracle version > 9 (and not for
> > any Postgres as well)
>
> > Thomas
>
> Never? It did at least in Oracle 7. As a well known and too many times
> used side effect. I remember having to reprogram queries because
> developed programs relied on the sort....
>
> Shakespeare

Oracle never sorted the result set, it sorted the table data to effect
grouping. That the result set retained the ordering enforced to
provide a group by result was, and is, a side effect of the
operation. In that regard Oracle still sorts (orders) the groupings,
they're simply in hash key order rather than column order. Still,
it's not the result set being ordered, it's the interim processing
doing the ordering. And, as stated earlier, it was merely a happy
coincidence that the one affected the other.


David Fitzjarrell
From: Michel Cadot on 2 Dec 2009 10:48

"Shakespeare" <whatsin(a)xs4all.nl> a �crit dans le message de news: 4b1613a0$0$22919$e4fe514c(a)news.xs4all.nl...
| Thomas Kellerer schreef:
| > Gene Wirchenko wrote on 01.12.2009 05:35:
| >> For example, a group by in SQL forces the result to be sorted by
| >> the grouping unless otherwise overridden.
| >
| > Never has been true. Group by does *not* sort the result. Not even in
| > Oracle 8 and and certainly not for any Oracle version > 9 (and not for
| > any Postgres as well)
| >
| > Thomas
|
| Never? It did at least in Oracle 7. As a well known and too many times
| used side effect. I remember having to reprogram queries because
| developed programs relied on the sort....
|
| Shakespeare

And you did it wrong, even in Oracle7.
Grouping needs some sort but in an internal (binary) one
which may not be a user sort (any country using a language with
national characters has experimented this).

Regards
Michel


From: Shakespeare on 2 Dec 2009 13:02
Michel Cadot schreef:
> "Shakespeare" <whatsin(a)xs4all.nl> a �crit dans le message de news: 4b1613a0$0$22919$e4fe514c(a)news.xs4all.nl...
> | Thomas Kellerer schreef:
> | > Gene Wirchenko wrote on 01.12.2009 05:35:
> | >> For example, a group by in SQL forces the result to be sorted by
> | >> the grouping unless otherwise overridden.
> | >
> | > Never has been true. Group by does *not* sort the result. Not even in
> | > Oracle 8 and and certainly not for any Oracle version > 9 (and not for
> | > any Postgres as well)
> | >
> | > Thomas
> |
> | Never? It did at least in Oracle 7. As a well known and too many times
> | used side effect. I remember having to reprogram queries because
> | developed programs relied on the sort....
> |
> | Shakespeare
>
> And you did it wrong, even in Oracle7.
> Grouping needs some sort but in an internal (binary) one
> which may not be a user sort (any country using a language with
> national characters has experimented this).
>
> Regards
> Michel
>
>

That is one of the reasons why we had to change it. Note that I did call
it a side effect.... and side effects are never to rely on..


Shakespeare
From: Mark D Powell on 4 Dec 2009 10:08
On Dec 1, 5:44 pm, Thomas Kellerer <OTPXDAJCS...(a)spammotel.com> wrote:
> Gene Wirchenko wrote on 01.12.2009 05:35:
>
> >      For example, a group by in SQL forces the result to be sorted by
> > the grouping unless otherwise overridden.
>
> Never has been true. Group by does *not* sort the result.
> Not even in Oracle 8 and and certainly not for any Oracle version > 9
> (and not for any Postgres as well)
>
> Thomas

Even back on version 7.x we experienced a group by query not returning
the result set rows in the same order as the group by expression.
Technically since at least the version 7 manuals the order by has
always been requried to ensure the order of the data returned. The
reality before 10g is that most statements using group by would return
the data in the group by order; however, with 10g and the hash group
by plan feature, the odds of the data not always returning in the
group by expression order have gone way up. I would suggest that
whenever the order that the result set is returned/displayed in is
important that the order by claude be made a manditory coding
practice.

HTH -- Mark D Powell --



First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: The job of a relational DBMS (was: Sql request)
Next: Pivot table Query - Need help in building correct logic