共计 5272 个字符，预计需要花费 14 分钟才能阅读完成。
这篇文章将为大家详细讲解有关数据库中如何批量删除数据，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
在一个 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
关于“数据库中如何批量删除数据”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
