大表的update更新（转）

因为业务需要对一张大表的一个列值进行update更新，表中有数据一亿多条，为了更新这一亿多条数据，我做了一下尝试，给各位同学留个前车之鉴。

表名：test

列名：name varchar2（50）

方法一：

直接对大表update，

语句：update test set name=replace(name,chr(13),’’)；

结果：执行12个小时候，sql仍未执行完成，于是在执行12个小时之后，我把这个交易kill 了；

后果：最终耗费了48小时来完成数据回滚，给数据库带来了严重的的压力，同时造成了大量的锁等待，结局很悲惨。

方法二：

吸收了方法一的教训，一次修改的话，undo数据太大，对数据库性能有严重的影响，书写了一个存储过程，每一万条数据提交一次，同时新建一张表t1和sequence sq_test，记录已经修改的条数，sql如下：

CREATE OR REPLACE PROCEDURE UpdateBigTableRecord AS

CURSOR c_cursor IS

select rowid from test;

v_rowid varchar(30);

x number := 0;

y number(30);

BEGIN

OPEN c_cursor;

LOOP

FETCH c_cursor

 into v_rowid;

EXIT WHEN c_cursor %NOTFOUND;

update test

  set name = replace(name, chr(13), '')

where rowid = v_rowid;

insert into t1(id) values (sq_test.nextval);

x := x + 1;

IF x > 10000 THEN

 COMMIT;

 x := 0;

END IF;

END LOOP;

CLOSE c_cursor;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

COMMIT;

END;

/

经过测过，此方法明显优于第一种方法，可以及时的释放锁资源，同时可以查看数据修改进度，经过测试，update速度约为每小时100万，这个速度 用来修改一亿条数据的话，估计需要100个小时，大概是4天4夜的时间。

结果：方法不适用，取消了

后果：又给数据库增加了undo压力

附带影响：磁盘阵列的IO等待严重，以至于后来在增加表空间时，速度缓慢，新增5M的表空间，耗时五分钟，大家可以想象一下大量的undo带来的后果，建议大家以后尽量回滚事物，尤其是对待一些大的事务。

方法三：

以上两种方法都是通过sql 使用update 来更新表中的数据，这种更新在带来大量的undo的同时也带来大量的redo，且速度缓慢。

最终通过下面的方法解决问题，create table as select…

sql如下：

create table BK_TEST

(

id,

name,

) parallel (degree 2)

as

select

id,

replace(name, chr(13), ‘’) name ,

from test nologging;

整个过程耗时00:03:59.66

方法四：

如果你觉得第三种方法创建新表将会耗费一些磁盘IO和磁盘空间，不愿意这么做，还有第四种方法：

在表之上创建视图。

sql：

create or replace view v1_test（name） as select replace(name, chr(13), ‘’) name from test;

alter table test add primary key(name);