inserting 2.5 million records.....

[Himanshu Bisht]

Hi just wanted to know that what are the few, most efficient, methods to insert approx 2.5 million records (in a flat file with 30 columns) in Oracle 8i.

Currently we use a java program which reads the file and inserts it in the table but it takes around 4 - 5 hours to perform this task.Moreover we have to to this activity twice so in total 10 hours.

I think there are ways (procedures reading the flat file) through which it can be done quickly.

Please suggest.

himanshu

[Paul Sturrock]

Is there a reason why you wrote your own application rather than just use SQL Loader? That's what I'd use. You might also try disabling indices before performing a bulk insert.

[Ajit Chandak]

You may also want to check direct path insert (providing

append

hint) in case you have to use custom code for this. Pls. verify the pros and cons of the direct path insert however...

[Himanshu Bisht]

well the main idea is to automate the whole process.

we tried the SQLLoader but it take the same time.

Want to know what will be the time taken by a fast application to insert 2.5 million records in an Oracle 8i table having 30 fields.

thanks

[Paul Sturrock]

SQL Loader (using a direct path load, as Ajit Chandak recommends) is the quickest way I know of getting data into Oracle. SQL Loader allows you to have multiple concurrent sessions loading data too. If you have tried SQL Loader with a concurrent direct path load and is is no faster than you own application, then there is not much you can do to speed things up I'm afraid.

Want to know what will be the time taken by a fast application to insert 2.5 million records in an Oracle 8i table having 30 fields.

Thats pretty much impossible to say without knowing more about your environment. SQL Loader is usually your benchmark though.

[Himanshu Bisht]

thanks Paul,

Well my environment is Windowds/jdk 1.5/Oracle 8i.

I know you are a busy man,still whenever you get time please just check these links and let me know wheather they are discussing the same thing or not.

http://home.clara.net/dwotton/dba/java_insert.htm

http://home.clara.net/dwotton/dba/big_insert.htm

thanks,
Himanshu
[ March 03, 2006: Message edited by: Himanshu Bisht ]

[Paul Sturrock]

Well my environment is Windowds/jdk 1.5/Oracle 8i.

Performance differences between OS probably exist, but when I mentioned environment I was more interested in memory, number/speed of processors, how your RDMBS is configured (character sets, indices, triggers etc.), if there is a network involved (and how fast it is if there is) etc. My point was the sort of question you asked:

Want to know what will be the time taken by a fast application to insert 2.5 million records in an Oracle 8i table having 30 fields.

is pretty much unanswerable by anyone but yourself. Test it and see. My point that SQL Loader is the quickest way I know to get data into Oracle, so if you want to know how your own application performs, compare it against the same data being loaded by SQL Loader.

A cursory glance suggests both those links seem to be talking about performance of bulk inserts via Java and PL/SQL, so they are pretty much in the same area as our discussion. Not sure how useful the Java programs are for your case though, since they perform all inserts within a single transaction. 2.5 million inserts in one transaction is going to be slow (since Oracle will need to keep track to what it has to potentially roll back). However, neither compare against SQL Loader.

The interesting line is right at the top of the PL/SQL article:

The intention was not to show how to load the maximum number of rows in the minimum amount of time (eg. using parallel loads etc.)

so I don't think its what you are looking for. And again "parallel loads" will probably mean SQL Loader (or a bespoke equivalent application, if you could be bothered writing it).

In my experience the only reason I'd write my own bulk loading application is if I needed to apply business rules to the data that cannot be easily done with SQL Loader. For example, if a "valid" record cannot be represented by DB constraints alone, I'd add some validation in my own loading app.
[ March 03, 2006: Message edited by: Paul Sturrock ]