Re: Mass modification of the data of a column

In article <41f4d468$0$26222$626a14ce_at_news.free.fr>, Robert says...
>
>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;
ouch, that would be a really slow way to do it. start at the top of this 20,000,000 row table -- read until you find 50,000 rows to update (each time you do this, you have to search further and further to find the 50,000) and then commit.

>
>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).
>

should take a couple of minutes and use relatively small disk space.

disk really is very inexpensive, when compared to the cost of data not being available, your time, your end users time. it really really is.

bite the bullet, just do it:

update big_table set the_column = decode(the_column,'AS','OW','RT','AR');

it'll be fast, consume the least overall resources, get the job done. if you have to, work with your DBA, get them to set you up a temporary "big" rbs for this one off operation. You'll need about 2gig or thereabouts (i'm assuming this bi-valued column is not indexed, if it is, drop it)

I just tested it on my desktop pc (i'm sure a real server has more resources than my desktop pc does...) big_table is a copy of all_objects over and over -- but I added a unique "id" column and "the_column" with your two values:

big_table_at_ORA9IR2> set timing on
big_table_at_ORA9IR2> update big_table set the_column = decode(the_column,'AS','OW','RT','AR');  

20000000 rows updated.  

Elapsed: 00:17:06.20

ops$tkyte_at_ORA9IR2> select used_ublk*8/1024 from v$transaction;  

USED_UBLK*8/1024

      1816.86719

Elapsed: 00:00:00.00

big_table_at_ORA9IR2> commit;  

Commit complete.  

Elapsed: 00:00:00.01

If you must do "no rollback", then

create table new_table
as
select <all_columns_but_that_one>, decode(the_column,'AS','OW','RT','AR') the_coluumn
from big_table;

create the indexes, grants, constraints, whatever on it, drop the old table rename new_table to big_table;

or do the CTAS, truncate, INSERT /*+ APPEND */ to put it back.

>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 ?

no and no.

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

no

>
>Any other ideas ?
>
>Thanks a lot in advance,
>
>Bobby
>
>

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