Fast way to load over a billion rows into Oracle from text files

Question

I have approximately 20 text files of data all in the same format and all tab delimited. These files are hundreds of megabytes each and all together I am expecting there to be about 1.2 billion rows of data.

My question - what is the best (and fastest) way to load these into an Oracle table? I attempted to load them via the built in import feature in TOAD, but that was only doing about 7,500 records a minute. At that rate, I'd be waiting a very long time for the import to complete.

I have no problem kicking off the process once for each input file, but I need a way or ideas on how to import these quickly.

Answer 1

Assuming you have the ability to copy these files to the database server, the most efficient approach should be to use external tables. You'd then just need to fire up a SELECT statement to load the data.