Best Practice for Bulk data load

[Guest_imported]

Each month I end up removing, inserting, comparing, and updating new data for about 90% of the records in a 1.5 million record partitioned table (basically remove most of the corresponding old records and replace with new records). Using SQLoader & stored procedures, it is done in batches throughout the month, no greater than 250k at a time. The approx load time for 250k records is 2hrs with moderate impact on users (24/7 shop & stored procedure SQL is optimized). Just found out we added a new client that has 2 million records that will also need to be removing, inserted, compared, etc. every month. Doing the math for 2 million records makes my head hurt!

Any other options out there that will reduce the impact of loading 2 millions record a month?
Any other options for bulk loading besides SQLLoader?

Anyone have experience using the EXCHANGE option for partitioned tables?

Thanks in advance & have a happy new year!

 

[jgaines1]

Try and use direct path loading with sql loader.
set DIRECT=true on the command line or in your par file.

http://www.orafaq.com/faqloadr.htm#SPEED

Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.

Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.

Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.

Run multiple load jobs concurrently.