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.

This is a FAQ. You will get your answer in the question I marked as duplicate. In your first query, there is no **ORDER BY** in the subquery, hence there is no **guarantee** of the order of the rows fetched. — Lalit Kumar B, Jun 18 '15 at 06:59
@Lalit Kumar B But for static table should rownum not be same everytime you fire query — Anand B, Jun 18 '15 at 07:02
**NO**, never. `ROWNUM` is a pseudo column which is assigned to a row after it passes the predicate phase. Rows are not stored in any order, neither they are fetched in any order. To ensure the order, you MUST always specify **ORDER BY**. — Lalit Kumar B, Jun 18 '15 at 07:04
@Lalit Kumar B But order by decreases performance. Is there any other way to do this without using order by — Anand B, Jun 18 '15 at 07:06
There is always an overhead with an explicit sort. On 12c you could use the new **Top-n** ow limiting feature, however, the additional cost to sort the rows will always be there. — Lalit Kumar B, Jun 18 '15 at 07:09
@Lalit Kumar B Another problem with sort by is I get a table space error when the number of records is in billions — Anand B, Jun 18 '15 at 11:58
@Lalit Kumar B, will it be possible to create a column in select clause on par with ROWNUM which will always have unique value for row in output result set — Anand B, Jun 18 '15 at 19:27

Khamill · Answer 1 · 2015-06-18T08:01:50.523

-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

edited Jun 18 '15 at 08:01

answered Jun 18 '15 at 07:02

Khamill

51
7

To limit the number of rows returned from a query to the 10 first rows of table T, use the following query: `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** – Khamill Jun 18 '15 at 07:33
@Khamill but will this remain constant when query is executed multiple times and no order by is required in query – Anand B Jun 18 '15 at 10:54

Behaviour of rownum in Oracle

1 Answers1