Re: Sort Order in Nested query

From: Jonathan Lewis <>
Date: Fri, 1 Feb 2013 11:07:52 -0000
Message-ID: <FE7A05B0AFC4481D83DF1D1CD7AF501B_at_Primary>

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(*)

    (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.


Jonathan Lewis

Author: Oracle Core (Apress 2011)

  • Original Message ----- From: "Fergal Taheny" <> To: "Gints Plivna" <> Cc: "oracle-l-freelists" <> 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
| 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

Received on Fri Feb 01 2013 - 12:07:52 CET

Original text of this message