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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
Sent: Wednesday, December 27, 2006 6:16 PM To: gkatteri_at_fastmail.fm
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