Re: Sort Order in Nested query

The most amusing example of the optimizer changing appeared in a white paper by the group that introduced the "version 2 sort" in 10g. To demonstrate the improved speed they tested a query against a table with a million rows:

select count(*)
from

    (select /*+ no_merge */ columnX from tableY order by columnZ)

Unfortunately by the time I read the white paper (and it have may been at the actual version when the new sort went on public release) the optimiser group had introduced an optimisation which allowed the optimizer to recognise that the ordering was irrelevant if the only requirement was to count the result set. So the sample SQL didn't sort the data - and the new version of Oracle got the result MUCH faster than the old version - for completely the wrong reason.

There is a more appropriate hint for your requirement: /*+ no_eliminate_oby */ to be safer you'd probably want /*+ no_merge */ in the inline view as well - but you still can't guarantee that the next version of the optimizer won't do something funny like postponing the sort.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Fergal Taheny" <ftaheny_at_gmail.com> To: "Gints Plivna" <gints.plivna_at_gmail.com> Cc: "oracle-l-freelists" <oracle-l_at_freelists.org> Sent: Friday, February 01, 2013 10:12 AM Subject: Re: Sort Order in Nested query

| Hi Gints,
| Yes query rewrite is exactly what I'm afraid will happen. Hadn't thought
of
| that hint but as you say that can't be relied on for certain. I'll remove
| the sequence from the query to work around the problem.
|
| Thanks,
| Fergal
|
|

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