Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Post the results please. –  aF. Jan 30 '12 at 11:36
add comment

migrated from stackoverflow.com Jan 30 '12 at 13:29

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
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 '12 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 '12 at 12:23
    
if it does anything different, the results could change. It's not deterministic unless you explicitly order –  Andrew Jan 30 '12 at 12:26
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
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 '12 at 11:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.