order by rownum — is it correct or not?

Question

I have a canonical top-N query against an Oracle database suggested by all FAQs and HowTos:

select ... from (
    select ... from ... order by ...
) where ronwum <= N

It works perfectly on Oracle 11, i.e. it returns top-N records in the order specified in inner select.

However it breaks on Oracle 12. It still returns the same top-N records, but they may get shuffled. The final order of these records is non-deterministic.

I googled but haven't found any related discussions. Looks like everyone else is always getting the correct record order from such select.

One finding was interesting though. I saw that some people use (without an explanation, unfortunately) an additional order by rownum clause in the outer select:

select ... from (
    select ... from ... order by ...
) where ronwum <= N
order by rownum

(both rownum's here are references to the Oracle pseudocolumn; it's not something returned by inner select)

It appears to work. But with Oracle optimizer you can never be sure if it's just luck or a really correct solution.

The question is: does order by rownum guarantee correct ordering in this case or not, and why? Me and my colleagues could not come to agreement about it.

P.S. I'm aware of other ways to select top-N records, e.g. using row_number analytic function and fetch first clause introduced in Oracle 12. I'm also aware that I can repeat the same order by ... on the outer select. The question is about order by rownum only — is it correct or not.

Answer 1

Inner query and outer query may or may not give different order and hence different order of rownum. As rownum is already ordered and if you want to get top N records then best thing is to do is create alias of rownum in inner query and use it on outer query.

select ... from ( select rownum rn ... from ... ) where rn <= N order by rn