批量删除数据方法-阿里云开发者社区

开发者社区> 苍雪明南> 正文

批量删除数据方法

简介: 在一个Oracle数据库运行过程中,有时候会遇到要批量删除数据的情况,如一个保存历史数据的表中有大量的数据已经不需要保留,要将这部分数据删除。通常采用的方法如下: 1、使用TRUNCATE命令进行删除。
+关注继续查看

在一个Oracle数据库运行过程中,有时候会遇到要批量删除数据的情况,如一个保存历史数据的表中有大量的数据已经不需要保留,要将这部分数据删除。通常采用的方法如下:

1、使用TRUNCATE命令进行删除。

如果是整个表的数据都要删除的话,使用TRUNCATE TABLE命令是理想的选择。它删除了表中的所有数据,并且因为不写REDO LOG FILE,所以速度很快。删除的同时,表的索引和约束条件仍然存在。这种方法适用于ORACLE的各个版本。但是当要删除的数据只是表中的一部分时,这种方法便行不通了。

2、直接进行DELETE操作。

直接使用DELETE命令进行删除,如果删除的数据量较大时,可能导致回滚段出错。这是因为在删除数据的过程中,不断扩展回滚段,直到回滚段的最大范围数或回滚段所在表空间空闲空间用完而出错。解决这个问题可以通过给删除数据的事务指定一个足够大的回滚段或者将回滚段所在表空间的AUTOEXTEND选项打开,同时将回滚段的MAXEXTENTS改大或设为UNLIMITED。不过这样仍存在一个隐患,如果删除的数据量大,同时数据库工作于归档模式下时,有可能导致日志切换频繁,所有日志文件都处于需要归档的状况,而归档进程来不及归档日志文件的情况出现,这时数据库将被挂起,直到有可用的日志文件后才恢复正常。

所以这种方法也不理想。

3、采用删除分区的方式。

比如若是按照时间做的分区表,drop partition删除分区的操作可能是效率最快的、最简单的。但是使用分区表的情况也不是很多。

下面介绍另外三种方法:

方法一:

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。

下面是我的删除过程,我的数据表可以通过主键删除,测试过DeleteFor all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。

首先创建一下过程,使用自制事务进行处理:(什么事自治事物,这里不过多阐述)

create or replace procedure delBigTab

(

p_TableName       in    varchar2,

p_Condition       in    varchar2,

p_Count        in    varchar2

)

as

pragma autonomous_transaction;

n_delete number:=0;

begin

while 1=1 loop

EXECUTE IMMEDIATE

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

USING p_Count;

if SQL%NOTFOUND then

exit;

else

n_delete:=n_delete + SQL%ROWCOUNT;

end if;

commit;

end loop;

commit;

DBMS_OUTPUT.PUT_LINE('Finished!');

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

end;

/

以下是删除过程及时间:

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  /

Procedure created.

SQL> set timing on

SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)

------------------

          11000000

Elapsed: 00:00:00.23

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.54

SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)

------------------

          11100000

Elapsed: 00:00:00.18

SQL> set serveroutput on

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');

Finished!

Totally 96936 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.61

10万记录大约19s

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000');

Finished!

Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');

Finished!

Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.85

SQL>

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');

Finished!

Totally 1000000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:03:13.87

100万记录大约3分钟

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');

Finished!

Totally 6999977 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:27:24.69

700万大约27分钟

以上过程仅供参考.

方法二:

通过一段PL/SQL程序循环分段删除数据,逐步提交事务,达到缩小事务规模,安全删除数据的目的。 

例如有一个数据表t_table,我们将对其中字段c_date满足小于200111日的记录进行删除,可以采用以下的PL/SQL程序。

1 DECLARE

2 V_TEMP NUMBER;

3 BEGIN

4 LOOP

5 BEGIN

6 SELECT 1 INTO V_TEMP FROM t_table WHERE c_date < to_date('2000/01/01','yyyy/mm/dd') AND rownum = 1;

7 DELETE FROM t_table WHERE c_date < to_date('2000/01/01','yyyy/mm/dd') AND rownum < 100;

8 COMMIT;

9 EXCEPTION 

10 WHEN NO_DATA_FOUND THEN

11 EXIT;

12 END;

13 END LOOP;

14 END;

程序的第1和第2行声明了一个临时变量。第4到第13行定义了一个循环,在这个循环中第6行不断检查表中是否还有满足条件的记录,如果有,第7行程序便执行删除操作,每次删除100记录,同时提交事务。当表中已无满足条件的记录时,便引起NO_DATA_FOUND的异常,从而退出循环。通过分批删除,逐步提交,缩小了事务的规模,从而达到避免出现回滚段错误的目的。然而这种方法依然存在因日志切换频繁,而归档进程来不及归档日志文件而导致数据库挂起的可能性。下面的程序通过ORACLE所提供的dbms_lock包中的过程sleep,解决了这个问题,从而达到安全快速大量删除数据的目的。

1 DECLARE

2 V_LOGNUM NUMBER; -- 数据库中拥有的日志文件数

3 V_NEEDARC NUMBER; -- 需要归档的日志文件数

4 BEGIN

5 SELECT count(1) INTO V_LOGNUM FROM V$LOG;

6 LOOP

7 LOOP

8 SELECT count(1) INTO V_NEEDARC FROM V$ARCHIVE;

9 IF V_NEEDARC < V_LOGNUM - 1 THEN

10 EXIT;

11 ELSE

12 DBMS_LOCK.SLEEP(60);

13 END IF;

14 END LOOP;

15

16 DELETE FROM t_table WHERE c_date < to_date('2000/01/01','yyyy/mm/dd') AND rownum < 100;

17 IF SQL%ROWCOUNT = 0 THEN

18 EXIT;

19 END IF;

20 COMMIT;

21 END LOOP;

22 END;

程序中的第2和第3行声明了两个变量v_lognumv_needarc来保存数据库中日志文件的数量和当前需要归档的日志文件数量。

5行获取了数据库中日志文件的数量。

6行到第21行开始了删除数据的循环,第7行到第14行是一个子循环,不断检测当前需要归档的日志文件的数量v_needarc是否小于数据库的日志文件总数v_lognum减去1,如果满足条件,则退出子循环,开始删除数据。否则的话便调用dbms_lock.sleep()过程,使程序休眠60秒,然后继续子循环,检测需归档的日志文件数量。

1719行,检查删除数据的结果,如果已无数据,则退出,程序结束。

这个程序,通过利用dbms_output.sleep()过程,在删除过程中当需要归档的日志文件达到认定的限制时,使删除过程暂时停止,等待ARCH进程将日志文件归档后再继续进行,从而达到避免归档日志文件来不及归档,导致数据库挂起的问题。

此方法适用于oracle的各个版本。

方法三:

使用NOLOGGING选项重新建表。 

ORACLE 8以后的版本中,CREATE TABLE命令提供了NOLOGGING的选项,在建表时不用写日志文件。

这样当我们在删除大量的数据时可以将要保留的数据通过CREATE TABLE ... NOLOGGING ... AS SELECT * FROM...的方法将要保留的数据备份到另一个表中,将原来的表删除,然后再 ALTER TABLE RENAME TO 命令将备份的表改为原来表的名字。

这个方法由于不写日志文件,所以速度很快,但是原来的表所拥有的索引和约束都将不存在,需重新建立。另外这个方法只适用于ORACLE 8以后的版本。

turncate table table1

因为truncate DDL操作,不产生rollback,不写日志速度快一些,然后如果有自增的话,恢复到1开始。

delete会产生rollback,如果删除大数据量的表速度会很慢,同时会占用很多的rollback segments,同时还要记录下G级别的日志。

1.选出您所需要保留的记录到新的表

Select * into Table2 From Table1 Where Time>='2006-03-10'

2.然后直接Truncate table Table1。无论何种恢复模式都不会进行日志记录

Truncate table Table1

3.最后对Table2进行改名为Table1

exec sp_rename 'Table2','Table1'


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
离线批量数据通道Tunnel的最佳实践及常见问题
基本介绍及应用场景 Tunnel是Odps提供的离线批量数据通道服务,主要提供大批量离线数据上传和下载,仅提供每次批量大于等于64MB数据的场景,小批量流式数据场景请使用DataHub实时数据通道以获得更好的性能和体验。
3527 0
一句代码实现批量数据绑定“.NET技术”[下篇]
  《上篇》主要介绍如何通过DataBinder实现批量的数据绑定,以及如何解决常见的数据绑定问题,比如数据的格式化。接下来,我们主要来谈谈DataBinder的设计,看看它是如何做到将作为数据源实体的属性值绑定到界面对应的控件上的。
577 0
easyui 删除数据表格
1 最直接的方法: 返回的数据格式               Object rows:Array[3] 0:Object 1:Object 2:Object length:3 __proto__:Array[0] total:3 return {total:0...
700 0
一句代码实“.NET技术”现批量数据绑定[上篇]
  对于一个以数据处理为主的应用中的UI层,我们往往需要编写相当多的代码去实现数据绑定。如果界面上的控件和作为数据源的实体类型之间存储某种约定的映射关系,我们就可以实现批量的数据绑定。为了验证这种想法,我写了一个小小的组件。
593 0
阿里云RDS mysql配置及迁移数据方法新手教程
阿里云RDS mysql配置及迁移数据方法新手教程
1029 0
一句“.NET技术”代码实现批量数据绑定[下篇]
  《上篇》主要介绍如何通过DataBinder实现批量的数据绑定,以及如何解决常见的数据绑定问题,比如数据的格式化。接下来,我们主要来谈谈DataBinder的设计,看看它是如何做到将作为数据源实体的属性值绑定到界面对应的控件上的。
587 0
存储数据恢复方法_infortrend ESDS RAID6故障导致数据丢失恢复方案书
[用户单位]XXX影音制作公司 [数据恢复故障描述] 一台infortrend ESDS-S12F-G1440存储,内接12块2TB硬盘组成RAID6,整个RAID6的所有空间划分给一个LUN,映射到WINDOWS系统上。
1169 0
+关注
苍雪明南
主流数据库:Oracle、MySQL、DB2 编程语言:Java、Python、shell 大数据工具:ZooKeeper、kafka
412
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载