Re: Mass modification of the data of a column

Robert wrote:
> 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 ?
Could lead to inconsistency if table is altered while you are ex- and importing...

>
> Is there a way to execute the update statement without using the
> rollback segment ?

Yes. Try
"ALTER TABLE <tableName> NOLOGGING" to suppress generation of redo logs.

>
> Any other ideas ?

Perhaps "ALTER TABLE <tableName> NOMONITORING" will help.

Regards,
Michael Schaefers Received on Mon Jan 24 2005 - 07:30:49 CST