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 8.1.7.4. 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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
