CODEWITHSUNDEEP
oracle-database
toolkit
toolkit

Reputation: 50247

alternatives for loading into oracle

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?

Upvotes: 2

Views: 4509

Answers (5)

David Aldridge
David Aldridge

Reputation: 52386

The only reasons why I'd use SQL*Loader rather than an external table are:

  1. If I couldn't access the data file from the server (even with preprocessing scripts to fetch it from an ftp site, for example)
  2. If external tables had a restriction that prevented it (for example not being able to load a stream formatted file).

External tables are far easier to use, more flexible (multitable insert, joins and/or aggregations during the load, merges, preprocessing scripts etc), and just a better experience all round. Parallel DML is also much more easy than SQL*Loader.

Upvotes: 2

Ari
Ari

Reputation: 157

The SQL Loader and external table approaches are pretty similar, both of them satisfy your requirement because they don't discard the entire set of data if some rows are rejected.

If you choose the SQL Loader approach you can use oracle-csv-loader to perform the task automatically instead of manually create the table and the control file.

Upvotes: 0

anon
anon

Reputation:

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.

Upvotes: 1

Mark Nold
Mark Nold

Reputation: 5698

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.

Upvotes: 1

WW.
WW.

Reputation: 24301

I would use an external table approach.

See asktom

Upvotes: 0

Related Questions