RE: 10g ORDER BY Performance

I've seen this a several times before - once on a large scale just a few weeks ago. I think the problem stems from a common misconception among developers (and probably some DBAs) that they can improve performance by omitting the ORDER BY clause and avoiding an unnecssary sort operation as long as they write their query to take advantage of an index that will return rows in the desired order. What they don't understand is that Oracle (and all other modern RDBMSs, I hope) is smart enough to recognize that rows are being returned from an index and it will automatically skip the sort operation on its own if it's not necessary. They also fail to recognize that the CBO could change the execution plan in the future for any number of reasons and then their data will not be sorted since they left off the ORDER BY. I'm not sure where these misunderstandings stem from, but I suspect they come from some old inaccurate book or white paper, another RDBMS, or maybe even earlier versions of Oracle on the RBO when the plan was much less volatile.

Regards,
Brandon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder

wow! haven't seen such mess before!

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Barnett
>
> 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.
>

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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