CODEWITHSUNDEEP
oracle-database
Jatin Tamboli
Jatin Tamboli

Reputation: 111

oracle query using pseudo column ROWNUM

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

Upvotes: 0

Views: 652

Answers (1)

Srini V
Srini V

Reputation: 11355

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)

Upvotes: 1

Related Questions