The following two statements (strongly simplyfied) returning a different result:

with a fix value for rownmun:

select * from table
 where rownum <= 12;

with bind variable for rownum:

select * from table
 where rownum <= :maxsize;

I know, Orcale is using a different execution plan due to internal statistics, but imho even if the choosen plans are different , the result should be the same. I'm using a Oracle 10g.

My dba skills are limited, therefore I would appreciate any hint or advice, that helps me to understand this strange behaviour.

  • Post the results please.
    – aF.
    Jan 30, 2012 at 11:36

3 Answers 3


The results would not be guarenteed to be the same if the execution plans are different. Without an explicit sub-query ordering the rownum will be based on the order in which Oracle selected the data from the tables / joined tables. If the plans differ on how the tables are joined and in which order, the first 12 values read will be different.

To make this more deterministic, use some kind of explicit ordering on a subquery and then rownum that on the outer query. It would be normal to want the first X records based on some explicit ordering, instead of 12 random ones.

  • the order by sort_criteria is too far in, when it does the left join, depending on the join mechanism used you could get different rows. Place the order by after the left join. e.g. one level before the rownum
    – Andrew
    Jan 30, 2012 at 12:15
  • thx for yor hint! here is a mor detailed abstraction of my query 'select * from ( select val1, val2 from (SUBSELECT order by sort_criteria) sub_res left join other_table ot on ot.id=sub_res.id) where rownum <= :max_size'. If the way oracle manages the left join differ, I get different result. Is that correct?
    – rontron
    Jan 30, 2012 at 12:23
  • if it does anything different, the results could change. It's not deterministic unless you explicitly order
    – Andrew
    Jan 30, 2012 at 12:26

Oracle does not guarantee the order of returned results unless you specify an ORDER BY in your select.


select * from MY_TABLE where rownum <= 12

may give different results on each execution, you're just asking for any 12 rows. Many times this will be the same (esp if Oracle can pull the results from buffer cache), but not guaranteed. Its not like popping 12 rows from a stack (which I think you assume here).


If you are using the literal word maxsize, this is reserved in Oracle and has specific meaning.

Consult the list of reserved words for more information.

  • 1
    thx for your awnser, but i get the same behaviour when using :some_name instead, so this should not be the problem.
    – rontron
    Jan 30, 2012 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.