Re: Large set-based UPDATE

f1fteen_at_hotmail.com (Paul) wrote in message news:<7b7286ec.0302120402.3a3818c8_at_posting.google.com>...
> 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.

As you are not issuing SELECT FOR updates, or issuing an explicit LOCK TABLE in row share mode, your lock will automatically escalate to a table level lock.
Setting up smaller transactions won't help, you need to have a LOCK TABLE in row share mode prior to your EXECUTE IMMEDIATE You may also want to review the execution path of the statement, checking whether or not it has a full table scan on the table to be updated.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Wed Feb 12 2003 - 08:53:56 CST