 | |
Re: [Oracle] Sql request
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: The job of a relational DBMS -
12-02-2009
, 02:55 AM
|
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. |
Quote:
|
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
#22
| |||
| |||
|
|
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.... |
http://asktom.oracle.com/pls/asktom/...74320098178823
http://tkyte.blogspot.com/2005/08/order-in-court.html
Thomas
#23
| |||
| |||
|
|
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 |
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
#24
| |||
| |||
|
|
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 |
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
#25
| |||
| |||
|
|
"Shakespeare" <whatsin (AT) xs4all (DOT) nl> a écrit dans le message de news: 4b1613a0$0$22919$e4fe514c (AT) news (DOT) 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 |
it a side effect.... and side effects are never to rely on..
Shakespeare
#26
| |||
| |||
|
|
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 |
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 --
#27
| |||
| |||
|
|
Gene Wirchenko <ge... (AT) ocis (DOT) net> writes: On Tue, 01 Dec 2009 13:45:44 +1100, Ben Finney bignose+hates-s... (AT) benfinney (DOT) id.au> wrote: What I was trying to express was that relational operations — like the various relational operations that ‘SELECT’ implements — should only return data as relations (they might also return status responses). They should never return non-relation data. * * *A relation does not have order. *This would not be a relation * * * * * select clicode,cliname from clients order by clicode because of the order by clause. Hmm. I'm not sure it's right to say the result would not *be* a relation; but I certainly take the point about ‘ORDER BY’ requesting order be imposed on an orderless relation. |
giving a seminar in Dallas next month:
http://books.google.com/books?id=406...age&q=&f=false
Quote:
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...omputer-fraud/
#28
| |||
| |||
|
|
On Nov 30, 8:52Â*pm, Ben Finney <bignose+hates-s... (AT) benfinney (DOT) id.au wrote: Hmm. I'm not sure it's right to say the result would not *be* a relation; but I certainly take the point about ‘ORDER BY’ requesting order be imposed on an orderless relation. Should anyone care, I ran across this while meditating upon Chris Date giving a seminar in Dallas next month: […] |
Yes, that about covers it. While ‘ORDER BY’ produces something that
isn't a relation, it clearly is an operation useful enough that it
belongs in a relational DBMS.
--
\ “If you do not trust the source do not use this program.†|
`\ —Microsoft Vista security dialogue |
_o__) |
Ben Finney
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.