Let's try this again ... without an ORDER BY, you are NOT guaranteed any
ordering ... indexing might work
in many, even most, situations, bu it just does not guarantee ordering.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"Koil Doss
\(ZADCO ITTS\) "
<oracledba-ezmlms To
hield-x5469955.x1 "LazyDBA Discussion"
[Email Address Removed] <[Email address protected]
.com> cc
09/28/2006 02:32 Subject
AM RE: select distinct trouble
(urgent)
Hi,
I had a similar situation. I created an index which covers the ORDER BY.
Fortunately this index was used at the end which solved the problem.
Why don't you think in that angle?
Regards,
Koil Doss
Database Administrator
-----Original Message-----
From: Martin Goodson
[mailto:oracledba-ezmlmshield-x38983829.[Email address protected]
Sent: 27 September 2006 11:20 PM
To: LazyDBA Discussion
Subject: Re: select distinct trouble (urgent)
The developers need shooting (metaphorically, of course). Oracle, as far
as I can recall (and I admit, I only go back to 7.3), have never
guaranteed a result set being returned in a specific order absent an
ORDER BY, and any competent developer should known that.
Surely not every one of those thousand statements would need rewriting,
though? I can't believe every single statement is dependent upon records
being returned in a specific order. It might take a lot of going
through, but it's more likely (I would hope!) that you could identify a
limited subset requiring ORDER BY clauses.
If this was a third-party package I would seriously consider going back
to the vendor. Relying upon any 'implied' order from the database is
shoddy work.
> All you can do is cuss at the developers ... without an ORDER BY, you
just
> can't count on
> controlling that order. You could put some sort of kludge in place
that
> seems to work, and
> years down the line someone else will cuss at you because their new
version
> of Oracle --
> or their migration to another RDBMS -- returns rows not in the
"correct"
> order.
>
> Bill
>
> Hi, Martin:
>
> Thank you for your advice! I exactly agree with you !
> But, this is not I can control, because application is developed
several
> years ago, we migrate the database recently and get the trouble.
> And I have to figure out what I can do without rewrite thousands SQLs
in
> application.
--
Martin
'Things need not have happened to be true. Tales and dreams are the
shadow-truths that will endure when mere facts are dust and ashes,
and forgot' - Dream Country, Neil Gaiman
blackberry: !lirameak![Email address protected]
(For spam protection the word wrapped in !s is spelt backwards)
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
============================================================================================================
This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail.
============================================================================================================
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page