Problem solve Get help with specific problems with your technologies, process and projects.

200,000-record load taking too long with SQL*Loader

We're trying to improve the performance of a SQL*Loader process that loads 200,000 records from a fixed-length text file into a table that contains 200 columns (number, varchar2 types only) -- all columns have values. We've disabled indexing, constraints, triggers, etc. We've fiddled with ROWS and BINDSIZE, but nothing seems to work. The load still takes about 1.5 hours on Sun 450/Oracle Should we use a PL/SQL or 3GL program to read/insert the data?

That seems quite odd to me as I did a test using a 200,000 record dataset and it was done in a couple of minutes. Without lots more details about your database configuration (where datafiles are located, etc), I'm not sure I can help. But, PL/SQL or some other programmatic way to load the data should not be any faster than direct inserts unless you can write PL/SQL to bulk bind your inserts... then maybe it might, but in theory, any programmatic way of manipulating data should be slower than directly inserting it (as with SQL*Loader).

I will suggest that you also look into turning off logging on the table you're trying to insert the new 200,000 records into. That will stop the generation of REDO and should speed the process up. Just issue and ALTER TABLE mytable NOLOGGING; command before running the load and then turn it on (LOGGING) afterwards. Make sure to do a backup upon completion of the load however if you use this method as you won't have any way to recover the inserted data in the event of instance failure.

For More Information

Dig Deeper on Oracle and SQL