Oracle returns different results when executing a sql with bind variable or with a fix value

Question

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.

Answer 1

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.

Answer 2

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

So,

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

Answer 3

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.