oracle query using pseudo column ROWNUM

Question

SELECT SOME_COLUMN_NAME_1
FROM (
      SELECT ROWNUM rnum, SOME_COLUMN_NAME_1
      FROM SOME_TABLE_NAME
      ORDER BY ROWID
)
WHERE rnum between 20001 and 30000

gives me 10000 rows between rownum 20001 and 30000 not sorted on rownum column

but

SELECT SOME_COLUMN_NAME_1 
FROM ( 
       SELECT ROWNUM rnum,a.SOME_COLUMN_NAME_1 
       FROM (
            SELECT SOME_COLUMN_NAME_1 
            FROM  SOME_TABLE_NAME
       ) a
)
WHERE rnum BETWEEN 20001 AND 30000

gives me 10000 rows between rownum 20001 and 30000 sorted on rownum column

plz explain

Do you mean the final output is not sorted? Understand this: unless you have an order by there is no guarantee of order. — Nick.Mc, Feb 20 '14 at 09:30
Like I say if you don't have an ORDER BY there is no guarantee of order. As for explanation, if you look at the query plan you'll probably see the final order is dictated by the index searches, seeks, joins etc. that it goes through to get the data. — Nick.Mc, Feb 20 '14 at 09:35
but in first case i have to use order by but in second case i m not using order by yet i get it in sorted order on rownum column how come?? — Jatin Tamboli, Feb 20 '14 at 09:43
http://stackoverflow.com/questions/11680364/oracle-faster-paging-query incase go through this link coz i m trying query paging in oracle — Jatin Tamboli, Feb 20 '14 at 09:45

score 1 · Accepted Answer · answered Feb 20 '14 at 11:28

The clue lies here

Oracle ROWID uniquely identifies where a row resides on disk. The information in a ROWID gives Oracle everything he needs to find your row, the disk number, the cylinder, block and offset into the block.

Rownum is a pseudo column. It is not always bounded to a record. It is just a number assigned sequentially in the same order it is fetched.

The difference between ROWNUM and ROWID is that ROWNUM is temporary while ROWID is permanent. Another difference is that ROWID can be used to fetch a row, while ROWNUM only has meaning within the context of a single SQL statement, a way of referencing rows within a fetched result set.

So in your first query, when you order by rowid and then filter by rownum, you get a

SELECT SOME_COLUMN_NAME_1
FROM (
      SELECT ROWNUM rnum, SOME_COLUMN_NAME_1
      FROM SOME_TABLE_NAME
      ORDER BY ROWID
)
WHERE rnum between 20001 and 30000

-- Rows fetched first and assinged a sequence 1,2,...N 
-- they are ordered by rowid (address of the record) make 
-- it a scrambled arrangement

In the second query,

SELECT SOME_COLUMN_NAME_1 
FROM ( 
       SELECT ROWNUM rnum,a.SOME_COLUMN_NAME_1 
       FROM (
            SELECT SOME_COLUMN_NAME_1 
            FROM  SOME_TABLE_NAME
       ) a
)
WHERE rnum BETWEEN 20001 AND 30000;

-- You select a set of rows (Order is preserved)
-- assign a sequence using rownum (Still order is preserved)

oracle query using pseudo column ROWNUM

1 Answers1