Shreyas Varun

Okay,
How about if I use this query to fetch 1000 records at a time from the db.

SQL query:
(select a.*, rownum rnum from (select id from Table_a where state= ? )a ) where rnum BETWEEN ? AND ?)

and then in the java code I do this

1. select count(*) from Table_a and store in a variable

int totalObjs = rs.getInt(1); List objs = new ArrayList<Obj>(); while(i <= totalObjs ){ ps.setInt(2, i); ps.setInt(3, i+1000); rs = ps.executeQuery(); while(rs.next()){ Obj obj = new Obj(); obj.setxxx(rs.getInt(1)); objs .add(obj ); } log.info("After processing"+objs .size()); i = i+1000 +1 ; objs .clear(); } }

Using this I am stating give me the objects between 1 and 1000 first
then 1001 and 2001 like that

Will that work ?

Thanks

Rahul,

Could you please elaborate ?
1. How will I get 5000 objects from the db at one time efficiently ?

Thanks for the suggestion for threading too. I will look into that as well.

But first please tell me how will I get 5K from db one time and then next time the next 5K and soon.

Thanks

Hi all,

I have a scenario in which I have to fetch 60-80K records from one schema, process them and then insert those into 5 different tables in another schema.

My question is how can I do these as "batches" or "sets of 1000s".

Two alternatives I was thinking about:

1. Read all the 80K objects and store them in a Collection. Then from the collection iterate through the collection and perform batch inserts in batches of 1000/5000 if possible. The disadvantage is this whole 80K will remain in the memory although processing is happening in batches.

2. If possible from the database itself read as batches of 1000/5000, so that only 5000 objects are being worked on at one time. I am thinking that in this case only 5K will remain in memory at a time.

But I dont know how to say get me these 80K records from Oracle in batches of 1000/5000.

Could you all please provide your thoughts ?

Is option 1 better or option 2 ?
If option 2 please provide ideas on how will I fetch records as batches.

Thankyou all
Shreyas