Suppose we have a table with 80 Million rows. Every day, I need to do a one-time update of 20 million rows - from a flat file. The table is range partitioned. And it has to be available for OLTP during the update. What's the best way to do it?
This is a proposed situation for a new application coming in. The vendor is doing the testing to see how fast it can be done. I would do some testing, but I don't have a server with Partitioning on it right now.
Loading using single update statements didn't work, obviously. The latest they tried was splitting the file into multiple files, each with 30K rows. Then using SQL*Loader to load each into a temp table and use a cursor to update the rows. They ran 100 processes at one time, and it took about 2 hours. We're still looking at other things.