select range of rows from a table

I have a table XYZ(ID Primary key,Name).The table is having more than 10000 rows. I want to process 1000 rows at a time.For this i have to first 1000 rows from the table and then update the table as per the results.For the first 1000 rows i can fetch using

   select *  from XYZ where rownum >=1 and rownum <= 1000

but for the next 1000 rows i do not know how to write the que开发者_如何学Cry because if i write

select count(*)  from XYZ where rownum >=1000 and rownum < 2000

the query gives 0 rows.Any idea how to write the query.Please help.I am using Oracle 11g database.

rownum isn't a real column, it's a pseudocolumn that is generated as rows are generated, starting at 1 and counting upward.

So, your query: select * from XYZ where rownum >= 1000 and rownum <= 2000

Returns no rows. Why? Oracle generates the first row and assigns it a rownum of 1. It then checks to see if it should be included in the result set. Rownum < 1000, so no. It goes on to the next row, and uses the next largest not yet used rownum ... which is still 1. Repeat until end of table.

You need to introduce a subselect to transform the rownum into a real column:

select * from
  (select rownum r, * from xyz) inner
where
  inner.r >= 1000 and inner.r <= 2000

To get the results you really want.