alternatives for loading into oracle

Question

I'm looking at loading a large amount of data into my oracle database.

The incoming data is CSV-like, and can be turned into CSV with a little parsing.

It may be the case that some rows are rejected during loading, but I dont want the other inserts to rollback.

I am considering one of the following approaches:

1. Using sqlloader
2. Using an external table
3. Using a stored procedure

Can anyone point me to some good URLs for pros/cons for each approach, and perhaps suggest any other alternatives I should consider?

Thanks.

Edit:

Sorry, should clarify 2. I am intending to create an external table, and then select into the database. My understanding is that this is a good approach for large volumes of CSV-like data?

Answer 1

I would use an external table approach.

See asktom

Answer 2

I've written Perl scripts to do this and used SQLldr.

I would say if you have access to the machine itself and you don't have complications in your CSV (CSV's can be a pain) then use sqlldr. It's worth spending the time working out the control files as it's fast and it's bound to be on each Oracle box you come across.

Answer 3

It really depends upon what you're doing with the data once you have it in Oracle. If you're going to be querying it frequently it would be more efficient to use sqlloader. By bringing the data into a regular Oracle table you get the ability to add indexes, a primary key, and so on. Also, since the data is managed by Oracle the performance may be better because the data is organized into a more rigidly defined structure.

External tables are really for convenience when you're going to be querying data infrequently on a set of external data. It saves the trouble of duplicating storage (the file and the oracle tablespace) and/or waiting for sqlloader to finish a batch job. However, I would typically use them for full table reads, since Oracle has no way to find a particular row.

Short answer: If you query this table with a "where" clause, make a table using sqlloader. If not, try both and evaluate performance.