RE: [SPAM] 10gR2 Upgrade .. Watch out

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 


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: