CODEWITHSUNDEEP
javasqloracle-database
nanpakal
nanpakal

Reputation: 1021

RowNum behavior

I have 6442670 records in a table and i am fetching them using jdbctemplate 1000000 at a time using row number. The following is the query 

select * 
from (select rowNum rn
             , e.* 
      from table_name e) table_name  
where rn >= ? and rn <= ?

I am fetching in eight iterations

  • 0 to 1000000
  • 1000001 to 2000001
  • 2000002 to 3000002
  • 3000003 to 4000003
  • 4000004 to 5000004
  • 5000005 to 6000005
  • 6000006 to 7000006
  • 6442669 to 7442669

After eight iterations I see only 6442668 records in list, and most of this are duplicate. Can fetching records using rowNum fetch the same record multiple times?

Upvotes: 0

Views: 185

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Try using row_number() over unique columns instead of ROWNUM.

SELECT *
  FROM (SELECT row_number() OVER ( ORDER BY unique_column_s ) rn,  e.*
          FROM table_name e ) table_name
 WHERE rn >= ? and rn <= ?

Upvotes: 1

Related Questions