Behaviour of rownum in Oracle

Question

I observed a strange behaviour with ROWNUM in Oracle with static tables. I am trying pagination with query like below

select * from (
     select the_data,rownum as seqn from the_table  
  )  where seqn <= somenumber and seqn >= othernumber

However when I execute query multiple times I observe that the rownum value returned is not unique for a row ie if a row appears at rownum 25 for the first time it appears at rownum 125 for second execution

This issue does not appear if I do a order by like,

select * from (
     select the_data,rownum as seqn from the_table order by column3 
  )  where seqn <= somenumber and seqn >= othernumber

However order by decreases performance. Is this normal or is there any other way to improve performance.

Answer 1

You should check oracle documents for this question. Followed links may be helpful for you http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions156.htm#SQLRF06100

One example: 
SELECT * FROM ( SELECT ROW_NUMBER() OVER () AS R, T.* FROM T ) AS TR WHERE R <= 10;

T is a table name. R is a row