Using SQL*Loader to update rows FAST - help!

This is the situation:

• A table with 4 million rows.
• A batch job that needs to update half of the rows ASAP (ideally in under 5 minutes). These rows are easily distinguished using one of the primary key columns.

I think SQL*Loader is the only way to get the rows in quickly enough, but it can only insert rows. Quoting the Oracle docs:

• Load your data into a work table [using SQL*Loader].
• Use the SQL language UPDATE statement with correlated subqueries.
• Drop the work table.

...which doesn't seem very fast. I've thought of deleting the 2 million rows first, but how fast is that? Won't it generate a lot of redo and the like? And will SQL*Loader reuse the deleted space (I think not)?

This can't be that uncommon. Is there a better way? Thanks for any input!

--Chris

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 12 2000 - 23:54:43 CST