1

I have two tables X and Y. when I run below query for table X

select * from 
( select rownum as rn, A.* from X as A order by A.IDdesc) where rn between 0 
and 1000000;

I get result as

rn  Id  name 
1   1   xxx
2   2   yyy
3   4   zzz

but for Y table when same query is executed I get result as

select * from 
    ( select rownum as rn, A.* from Y as A order by A.IDdesc) where rn between 0 
    and 1000000;

rn  Id  name 
5   1   xxx
7   2   yyy
10  4   zzz

rn in Y table is getting some random numbers. Not able to understand this behavior. ny help would be appreciated.

SYMA
  • 119
  • 3
  • 15

1 Answers1

2

The ROWNUM pseudo-column is added to the selected columns in the order Oracle retrieves the rows and then the ORDER BY clause is applied. What you appear to want is to order the rows first and then generate the ROWNUM against this ordering but that is not what your query will do.

For table X, it happens that Oracle retrieves the rows in the same order as the id column. However, for table Y, the rows are not retrieved in the same order as the id column so the ROWNUM is in the order the rows were retrieved and then a different ordering is applied so the ROWNUM values appear shuffled.

What you should do is to apply the ORDER BY before generating the ROWNUM pseudo-column so they are in-sync:

SELECT *
FROM   (
  SELECT rownum as rn,
         A.*
  FROM   (
    SELECT *
    FROM   X
    ORDER BY ID desc
  ) A
)
WHERE  rn BETWEEN 0 AND 1000000;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • So do we know why for table A it is retrieving in same order as id but same thing not happening for table B. Because with two subquery,performance would degrade. – SYMA Aug 10 '18 at 10:17
  • @SYMA No, it would not degrade performance; you are not changing the operations Oracle is performing you are doing exactly the same operations in a different order. – MT0 Aug 10 '18 at 10:23
  • ok.. instead of using two subquery can i use row_number. Would it improve performance? – SYMA Aug 10 '18 at 10:28
  • @SYMA Try it and see - but I would not expect performance to improve; at best I would expect identical performance. – MT0 Aug 10 '18 at 10:32