前些日子，开发让我删除某个表08年以前的数据（delete from t1 where to_char(time,'yyyymmdd')<'20080101';）。首先，我查了下需要删除的行数，

大约有140多万行，开发这时告诉我，他删除时报ORA-01555，这个当然很正常，回滚段设的不够大。

后来，我查了相关资料，基本有两种方法

第一种:可以先建一个临时表，把t1表中大于20080101的数据倒入到临时表，然后truncate掉t1表，然后再把临时表的数据插入t1表。

测试过程如下：
SQL> conn scott/tiger

已连接。

SQL> create table t1 as select * from emp;

表已创建。

SQL> insert into t1 select * from t1;

已创建14行。

SQL> /

已创建28行。

SQL> /

已创建56行。

SQL> /

已创建112行。

SQL> /

已创建224行。

SQL> /

已创建448行。

SQL> /

已创建896行。

SQL> /

已创建1792行。

SQL> /

已创建3584行。

SQL> /

已创建7168行。

SQL> /

已创建14336行。

SQL> /

已创建28672行。

SQL> /

已创建57344行。

SQL> /

已创建114688行。

SQL> /

已创建229376行。

SQL> /

已创建458752行。

SQL> /

已创建917504行。

SQL> select count(*) From t1;

COUNT(*)

----------

1835008

SQL> select distinct empno from t1;

EMPNO

----------

7782

7839

7844

7698

7521

7902

7566

7654

7788

7934

7499

EMPNO

----------

7876

7369

7900

已选择14行。

SQL> update t1 set empno=6666 where empno<7900;

已更新1441792行。

以上步骤构造实验表，共183多万行，其中empno小于7900的有144多万行，

打算删除这144多万行

SQL> select distinct empno from t1 order by empno;

EMPNO

----------

6666

7900

7902

7934

SQL> create table tmp_t1 as select * from t1 where empno>6666;

表已创建。

将empno不是6666的数据导入到临时表tmp_t1中

SQL> select count(*) From tmp_t1;

COUNT(*)

----------

393216

SQL> select distinct empno from tmp_t1 order by empno;

EMPNO

----------

7900

7902

7934

SQL> truncate table t1;

表被截断。

将t1表截断

SQL> select count(*) From t1;

COUNT(*)

----------

0

SQL> insert into t1 select * From tmp_t1;

已创建393216行。

将保留下的数据从tmp_t1表导入到t1

SQL> select count(*) From t1;

COUNT(*)

----------

393216

SQL> select distinct empno from t1 order by empno;

EMPNO

----------

7900

7902

7934

验证结果正确

但是，这种方法不太适用于OLTP，因为每秒钟表的都有数据变化，很可能截断表时有数据插入，所以我最后用的是第二种方法。

第二种：分批删除，逐次提交。

具体测试过程如下：

SQL> create table t2 as select * from emp;

表已创建。

SQL> insert into t2 select * From t2;

已创建14行。

SQL> /

已创建28行。

SQL> /

已创建56行。

SQL> /

已创建112行。

SQL> /

已创建224行。

SQL> /

已创建448行。

SQL> /

已创建896行。

SQL> /

已创建1792行。

SQL> /

已创建3584行。

SQL> /

已创建7168行。

SQL> /

已创建14336行。

SQL> /

已创建28672行。

SQL> /

已创建57344行。

SQL> /

已创建114688行。

SQL> /

已创建229376行。

SQL> /

已创建458752行。

SQL> /

已创建917504行。

SQL> select count(*) FRom t2;

COUNT(*)

----------

1835008

以上步骤构造实验表，共183多万行

SQL> select distinct empno from t2;

EMPNO

----------

7782

7839

7844

7698

7521

7902

7566

7654

7788

7934

7499

EMPNO

----------

7876

7369

7900

已选择14行。

SQL> update t2 set empno=8888 where empno<7900;

已更新1441792行。

这样做为了便于删除t2表中的大部分行

SQL> create or replace procedure delBigTab

2 (

3 p_TableName in varchar2,

4 p_Condition in varchar2,

5 p_Count in varchar2

6 )

7 as

8 pragma autonomous_transaction;

9 n_delete number:=0;

10 begin

11 while 1=1 loop

12 EXECUTE IMMEDIATE

13 'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'

14 USING p_Count;

15 if SQL%NOTFOUND then

16 exit;

17 else

18 n_delete:=n_delete + SQL%ROWCOUNT;

19 end if;

20 commit;

21 end loop;

22 commit;

23 DBMS_OUTPUT.PUT_LINE('Finished!');

24 DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');

25 end;

26 /

过程已创建。

SQL> set timing on

SQL> exec delBigTab('T2','EMPNO=8888','10000');

PL/SQL 过程已成功完成。

已用时间: 00: 02: 49.82

删除144多万行数据，不到3分钟

SQL> select COUNT(*) fROM t2;

COUNT(*)

----------

393216

已用时间: 00: 00: 01.12

SQL> select distinct empno from t2;

EMPNO

----------

7902

7934

7900

已用时间: 00: 00: 01.35

验证数据，已删除

http://www.itpub.net/viewthread.php?tid=934825&extra=&highlight=%2BAlienovo&page=1

ITPub博客

Delete大批量数据的方法

最新文章