Large set-based UPDATE

Hi all,

I am reviewing an Oracle stored procedure (BULK_UPDATE) that makes a large set-based update to a large table (ACCOUNTS, 5 million rows).

The problem we are encountering with this is that, because the UPDATE statement applies changes to a large number of rows at a time to the ACCOUNTS table (around 150,000), a web layer that also allows updates to this table is being regularly locked out for an unacceptable length of time.

To summarise the code:

An UPDATE statement is constructed within a stored procedure and executed using EXECUTE IMMEDIATE

This UPDATE statement affects around 150,000 rows of a 5,000,000 row table

I was thinking a possible solution to this is to break the 150,000 row set based update into 150,000 updates to single rows by processing them through a cursor. I would then COMMIT within the cursor every 1000 rows.

Is this a good way to go?

Am I right in thinking that only 1000 rows at a time will be locked using this method?

Is this likely to be much slower given that I am now updating 1 row at a time through a cursor?

All help is gratefully received.

Paul. Received on Wed Feb 12 2003 - 06:02:13 CST