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

oracleoracle-10g

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.

Best Answer

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.

Related Question