数据库中如何批量删除数据

这篇文章将为大家详细讲解有关数据库中如何批量删除数据，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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

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

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

2、直接进行DELETE操作。

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

所以这种方法也不理想。

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

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

下面介绍另外三种方法：

方法一：

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

下面是我的删除过程，我的数据表可以通过主键删除，测试过Delete和For 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满足小于2001年1月1日的记录进行删除，可以采用以下的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_lognum和v_needarc来保存数据库中日志文件的数量和当前需要归档的日志文件数量。

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

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

第17到19行，检查删除数据的结果，如果已无数据，则退出，程序结束。

这个程序，通过利用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'

关于“数据库中如何批量删除数据”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。