RE: 10g ORDER BY Performance

From: Kerber, Andrew W. <Andrew.Kerber_at_umb.com>
Date: Fri, 24 Aug 2007 14:03:26 -0500
Message-ID: <D40740337A3B524FA81DB598D2D7EBB3097A8969@x6009a.umb.corp.umb.com>

I think it is a futile effort. What happens when you go live, and you have to update a record or two? There is now way the data will continue come back in the desired order unless you add order bys.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Barnett Sent: Friday, August 24, 2007 8:48 AM
To: Oracle-l
Subject: 10g ORDER BY Performance

First, the disclaimer, someone made a very bad coding decision! Now the question is how to get out of the mess.

We have about 2,000 scripts that do not have ORDER BY clauses anywhere in them. In 9i all of the queries returned the rows in the correct order. More a lucky accident than anything else.

In 10g the optimizer has changed and the results are returned unordered. Since we are weeks away from go live of a major project there is no time to edit and test all of the scripts. Management is now wanting us to go live on 9i rather than 10g. Something the DBA team is really resisting but may be necessary.

I came across a _newsort_enabled parameter which did not change the result set in 10g. Is anyone aware of a parameter that can be set in 10g that will give the 9i behavior other than setting the
optimizer_features_enabled to 9.2? If we do that we might as well be on 9i.

Pete Barnett

Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool.
http://autos.yahoo.com/carfinder/
--

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

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 24 2007 - 14:03:26 CDT