You should never assume that the index will be used. The optimizer can
choose not to use an index at any time and not tell you (creating an
unpleasant user experience). Even a hint will not guarantee the index
will be used.
Order by is your friend.
Amy Loukota
Oracle Certified DBA
TriWest HealthCare Alliance
15451 N. 28th Ave.
Phoenix, AZ 85053
[Email address protected]
work: 602-564-2452
Smile! It's another day under the big top
and there is no shortage of clowns!
-----Original Message-----
From: Koil Doss (ZADCO ITTS)
[mailto:oracledba-ezmlmshield-x5469955.[Email address protected]
Sent: Wednesday, September 27, 2006 11:33 PM
To: LazyDBA Discussion
Subject: 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
The information contained within this transmission is proprietary and protected pursuant to federal regulations; it is intended only for the use of the individual or entity to which it is addressed.
If you are not the intended recipient, you are hereby notified that any distribution, copying, disclosure, use of, or reliance on, the contents of this transmission is strictly prohibited.
If you have received this communication in error, please notify the sender immediately by telephone and permanently delete the original message, attachments and all copies. Thank you.
Oracle LazyDBA home page