Re: Mass modification of the data of a column

Hi Robert,

Export/import with help of SQLPlus/SQLLoader will be much faster. But please make sure that there are no other updates during export/import process otherwise you can lose changes made after export. Also this method must be improved if you have got foreign keys referencing the table:

Try to export data to ASCII file as:

select decode(c1,'AS','OW','AR','RT',c1),c2,c3 from tab1

Truncate table and then import using SQL Loader:

Control file:
OPTIONS(direct=true)

LOAD DATA INFILE 'tab1.txt'

INTO TABLE tab1

INSERT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (C1,C2,C3)

SQL Loader command:

sqlldr userid=scott/tiger_at_ora control=tab1.ctl

-- 
Best regards, Dmitry Tolpeko
SQLWays - Database transfer tool for Oracle, DB2, SQL Server, Informix, 
Sybase and MySQL
www.ispirer.com


"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

>

>