Re: Big Update on Busy Table

1. Is it really going to take too long to just get it over and done with on the live table? Perhaps an off-hours one-shot transaction? I've occasionally rethought my bulk operations and realized that the overall hit might not be 'that bad', but your situation is, of course, unknown to me.
2. How are you planing on getting those 'few thousand rows at a time'? And how are you planning on identifying those which have been done and are not do be repeated?
3. Is this something that can be handled through PL/SQL's bulk operations? Perhaps with a "SELECT FOR UPDATE OF"? (http://morganslibrary.com/reference/plsql/array_processing.html#apbc and other areas of Morgan's Library)

/Hans

On 16/07/2014 3:07 PM, Charlotte Hammond wrote:
> Hi All,
>
> I need to run an update of one column of millions of rows in a busy
> OLTP table. To prevent the transaction blocking other sessions for
> too long I'm planning to break it into updates of a few thousand rows
> at a time with a commit in between. This will prevent any one row
> being locked for more than about a second. However it is tending
> towards slow-by-slow processing: is there a better way of updating
> this table without creating long lived TX locks?
>
> Thanks!
> Charlotte

--
http://www.freelists.org/webpage/oracle-l