Re: Mass modification of the data of a column

In article <ct2tab$r0k$1_at_f1node01.rhrz.uni-bonn.de>, Michael Schaefers says...
>
>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.
>

that works for very very specific operations such as:

create table as select
insert /*+ APPEND */ (and then only for the table, not any indexes) direct path loads (same caveats)
alter index rebuild

and so on. never for update, never for delete.

>>
>> Any other ideas ?
>Perhaps "ALTER TABLE <tableName> NOMONITORING" will help.
>

that would have no effect either, not as far as performance goes anyway.

>Regards,
>Michael Schaefers

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation