Re: Mass modification of the data of a column

"Robert" <rober_at_sansadresse.com> wrote in message news:41f4d468$0$26222$626a14ce_at_news.free.fr...
> Hi all,
>
> I would like to modify the content of a column in a large table.
>
> The column contains two distinct values : AS or AR
> and each value must be changed to OW (for AS) and RT (for AR).
>
> The table contains about 20 000 000 rows.
>
> I tried to modify the values with a PL/SQL procedure that did nearly this
:
> LOOP
> update tablename set col='OW' where col='AS' and rownum < 50000;
> IF (SQL%ROWCOUNT = 0) then
> commit;
> exit;
> end if;
> commit;
> END LOOP;
>
> If I choose a bigger value for the rownum clause, I usually raise the
> "rollback segment to small" error.
> So the procedure is very long to complete (hours or days).
>
> So, I am looking for a quicker way to make these mass modifications...
>
> I has the idea to export the table content to a file, change the values
> then re-import the file. Will it be quicker ? safe ?
>
> Is there a way to execute the update statement without using the
> rollback segment ?
>
> Any other ideas ?
>
> Thanks a lot in advance,
>
> Bobby
>
>

Some other things to consider, not mentioned by others:

Are there any Update triggers on the table? Do you need them to fire for this operation? If not, and no one else will be using this table during the update, disable them (remember to re-enable them).

How about indexes? Is this column indexed? It is sometimes faster to drop the index, do the update, and then re-creaste the index. Received on Mon Jan 24 2005 - 09:03:47 CST