Get 1000 row set from Oracle table

Question

I want to select 1000 rows at a time using the query:

SELECT * FROM MEMBERID_1M WHERE ROWNUM <1000

How do I get the next set of 1000 rows from this table in a for loop?

Answer 1

I suggest you to use ROW_NUMBER() function like this: (my id is your PK)

SELECT M.* 
FROM (
    SELECT MEMBERID_1M.*, ROW_NUMBER() OVER (ORDER BY id) As rn
    FROM MEMBERID_1M ) M
WHERE 
    (rn <= 1000)

And for next:

SELECT M.* 
FROM (
    SELECT MEMBERID_1M.*, ROW_NUMBER() OVER (ORDER BY id) As rn
    FROM MEMBERID_1M ) M
WHERE 
    (rn > 1000) AND (rn <= 2000)

For page :i:

SELECT M.* 
FROM (
    SELECT MEMBERID_1M.*, ROW_NUMBER() OVER (ORDER BY id) As rn
    FROM MEMBERID_1M ) M
WHERE 
    (rn > :i * 1000) AND (rn <= (:i + 1) * 1000)

Answer 2

Reproducing the answer

There is only a rather convoluted way to do this, which is a real pain with Oracle. They should just implement a LIMIT/OFFSET clause...

The rownum gets assigned after the row has been selected by the where clause, so that a rownum must always start with 1. where rownum > x will always evaluate to false.

Also, rownum gets assigned before sorting is done, so the rownum will not be in the same order as your order by says.

You can get around both problems with a subselect:

 select a,b,c, rn from 
    ( select a,b,c, rownum rn from 
         ( select a,b,c from the_table where x = ? order by c)
      where rownum < Y)
  where rn > X

If you do not need to sort (but only then), you can simplify to

 select a,b,c, rn from
     ( select a,b,c, rownum rn from the_table where rownum < Y )
   where rn > X

Answer 3

You should be paging on server side .use this query and more detail refer this link http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

select * 
  from 
( select rownum rnum, a.*
    from (SELECT * FROM MEMBERID_1M ) a
   where rownum <= :M )
where rnum >= :N;