What is more efficient INSERT command or SQL Loader for bulk upload - ORACLE 11g R2

Question

As part of a new process requirement, we will be creating table and which will contain approximately 3000 - 4000 records. We have a copy of these records in plain text on a txt file.

Loading these records in the table leaves me with two choices

1. Use a shell script to generate SQL file containing INSERT Statements for these records

   • with the use of awk, shell variables, and loops to create a sql and script execution of this sql, we can be performed with ease

2. Use of SQL Loader.

   • Realignment of the record list and ctl file generation the only dependency.

Which of the above two options would be most efficient, in terms of taking up DB resources, utilisation on the client server on which this is to be performed.

I do realise the number of records are rather small, but we may have to repeat this activity with higher number of records (close to 60,000) in which case I would like to have the best possible option configured from the start.

Cauthen

I'm assuming that when you say "more efficient" you mean "will take less time to execute." If so, then SQL Loader will usually be faster than many INSERTs. – Skirling

Andy, More than the time it takes to execute, I would like to minimise the impact (in terms of resource utilisation) it causes on the System. – Medina

Answer 1

SQL*Loader is the more efficient method. It gives you more control. You have an option do DIRECT load and NOLOGGING, which will reduce redo log generation, and when indexes have been disabled (as part of direct loading), the loading goes faster. Downside, is if load is interupted, indexes are left unusable.

But, considering the advantages, SQL*Loader is the best approach. And you will feel the difference, when you have millions of records, and having so many loading jobs running in parallel. I heard DBA complaining about the log size, when we do CONVENTIONAL INSERT statement loading, with 200+ such jobs, running in parallel. The larger the data volume, the larger the difference you'll see in performance.

Stomatitis

Thank you. This is quite helpful. Operating on an prod enviornment, I would like my activities to have minimum impact on the system. – Medina

Do you think SQL loader will add some dependency on Oracle. If I need to migrate my DB to some other RDBMS later, I will be looking for some other loader or writing custom code anyway. I understand it is not an efficient way(not more than SQL loader) to write insert statements, but if my data-to-be-loaded will not be more than 30k and portability is preference, do you still recommend SQL loader? – Althing

@KayGee 30K is not a big deal at all for any database. So, you can otherwise think for a global solutions. But loading a table, cant be global I guess. – Stomatitis

Answer 2

SQL*Loader will be more efficient than thousands of individual INSERT statements. Even with 60,000 rows, though, both approaches should complete in a matter of seconds.

Plagiarize

Answer 3

Of the two options you mentioned, SQL*Loader is definitely the way to go - much faster and more efficient.

However, I'd choose another approach - external tables. Has all the benefits of SQL*Loader, and allows you to treat your external csv file like an ordinary database table.

Unmuzzle

Frank, Thanks for the suggestion, in my opinion External tables would not suit my need. I had tested the proc with External Tables, but any operations performed on the external table generates log files at the source file location, this increases my maintanance activity. Plus we would have to ensure the integrity of the source file, which is easier if the data is on a db datafile rather than a CSV/TXT file. Also I cant perform RMAN backup or DML operations on external tables. If your suggestion was to create a tmp table and create new table using tmp table as reference, it would be redundant. – Medina

@VenusD'souza Sounds sensible - do whatever makes the most sense for your requirements. – Unmuzzle