reading and writing large data(200000) from oracle

[vasu mannem]

Hi Everyone,
here I have one problem ...
read data from three different tables A,B,C total 200000 and process each record and then insert these 200k records in to another oracle table.

currently ,iam using batch program to read and write the data ..

batch program calls java/ejb then call three daos to get the data..then validation of each record and then finally storing the data in to another table..

i tested for 250 records for the same process then it took 5 minutes ..and wondering how much time it will take for 200000 record.
i think 66 hours for complete job with 200000 records...

can any one help me the best process to reduce the time for this kind of job?

here is my current process...

read data from table a (100000 records)

read 1000 ,process ,then insert.

same process from table b and c.

[Srikanth Basa]

Is there any specific reason why you aren't considering PL/SQL procedures for performing validation of the batch of records ?

[Ilja Preuss]

The first thing to do would be finding out what's taking so long. Is it validation? Populating the EJBs? Writing them back to the database?

[Tim Holloway]

Probably the #1 performance killer when doing a bulk database load is indexes. It's usually advisable to load the tables flat first, and only afterwards build indexes on them. Otherwise the load process spends inordinate amounts of time rebalancing the indexes during the load process.

Unless there's some compelling reason to do so, I'd skip Java for this kind of operation and use the native database utilities. You can load a table from another table in Oracle using a CREATE table from SELECT statement, although this may not be sufficient if your validation process is complex. For more complex variants, I'd generally either build a set of the keys of the good records and use that or else copy everything and tag or delete the bad stuff. Depending on what worked best for the situation.

[Indumuni Abeyaratna]

if possible break this process into multiple transactions. Single transaction could take time since it had to keep track of all what it had done.

[Tim Holloway]

Bulk loader utilities often split the difference. Setting up and committing a transaction is a fair amount of overhead, but as Indumuni mentioned, so is keeping a large work-in-progress/rollback queue.

So it's common for the bulk loaders to run in something like 1000 records per transaction.