Limit no of records fetched by a query.

Question

I have a table with 100 K records. I am writing an XML file based on this recordset. I need to write 100 records to a file at a time. So I will have 1000 separate files.

Currently to limit number of records getting written to a file, I am using the

SELECT * FROM TABLE WHERE ROWNUM  < 100;

This fetches 100 records and writes them to a file. When I do this again, it will fetch the same 100 records once again. Is there some way of eliminating the records it has already written?

I thought of creating a table where I will insert the primary key of each record that has been written to a file. So I will then do

SELECT * FROM TABLE WHERE ROWNUM < 100 AND PRIMARYKEY NOT IN (SELECT PRIMARYKEY FROM ANOTHERTABLE);

I am using Oracle 9i and a console based c# app. I use ODP .NET to make the connection.

Is there any other way to do this process?

Answer 1

First of all, your select statement should be ordered, otherwise you aren't guaranteed to get the same 100 rows every time.

What you could do is have a dynamic query (if you don't want a stored procedure than just build the string and subsitute :start and :end with actual numeric values before executing the query) that limits rownum, maybe something like:

select *
from(
    select table.*, rownum rn
    from table
    where rownum rownum < :end
    order by ID) s
where s.rn >= :start

UPDATE

I do agree with Scott Anderson, why not just get all records at once and separate the records into groups of 100's in your front-end system?

Answer 2

A general solution for limiting the records fetched by Oracle has been given by Tom Kyte.

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= MAX_ROWS )
 where rnum >= MIN_ROWS

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:127412348064

I also agree with Scott Anderson that in this case just fetch all the records and use a couple loops.