Re: Big Update on Busy Table
Date: Wed, 16 Jul 2014 19:03:04 -0300
Message-ID: <mjig6w6pu3h5e3q49m7en8es.1405548184289_at_email.android.com>
How about a CTAS and a rename? Remember, this action needs duplicate storage usage on the table. If this is not a problem, this is one of faster ways to achieve this goal.
All the Best,
Mufalani
Enviado por Samsung Mobile.
<div>-------- Mensagem original --------</div><div>De : Hans Forbrich <fuzzy.graybeard_at_gmail.com> </div><div>Data:16/07/2014 18:36 (GMT-03:00) </div><div>Para: oracle-l_at_freelists.org </div><div>Assunto: Re: Big Update on Busy Table </div><div>
</div>A couple of thoughts:
- 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.
- 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?
- 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-lReceived on Thu Jul 17 2014 - 00:03:04 CEST