RE: [SPAM] 10gR2 Upgrade .. Watch out
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>, <gkatteri@xxxxxxxxxxx>
- Date: Wed, 27 Dec 2006 18:24:12 -0500
I agree. I just don't understand how many times this topic has to said "if
you want data ordered use an order by otherwise there no guarantee to the
order". Everyone at Oracle says, every good oracle professional repeats it.
The CBO won't do an unnecessary sort if it knows the data is sorted
correctly due to the use of an index or such.
If the order by puts so much over head that query doesn't run as fast you
would like then there are bigger issues at play.
Sorry for my rant but this is a big pet peeve of mine. I didn't realize this
issue was just a non issue regarding sort, initially I misread it as wrong
data returning.
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfgang Breitling
Sent: Wednesday, December 27, 2006 6:16 PM
To: gkatteri@xxxxxxxxxxx
Cc: Mark J. Bobak; oracle-l
Subject: Re: [SPAM] 10gR2 Upgrade .. Watch out
At 03:42 PM 12/27/2006, GovindanK wrote:
>You are correct .. order of output is not guaranteed unless one
>specifies an 'order by' clause explicitly .. but it certainly looks
>unacceptable when the default behaviour changes across upgrades .. in
>9iRel2 Oracle was doing the sort implicitlly (which should not have been
>the case) and they left it high and dry in 10gRel2 reverting back to the
>"expected" behaviour .. this results in the mismatch of the output order
>across upgrades.
As far as I'm concerned it merely exposes a bug in your application
code, relying on group by to return the result set in a certain
order. Just because it happens to work in some cases (here Oracle pre
10gR2) does not make it the default behaviour. It was just a side
effect of an implementation detail. The default behaviour is that
without the order by caluse Oracle can return the resultset in any
order it darn well pleases, including in the group by order, but it
doesn't have to. And Oracle warned all along not to rely on it.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
