plsql删除大量数据_三种PLSQL批量删除数据方法的测试
测试表
craete table tbl_c(id number, name varchar2(1), insert_time date);
create index idx_tbl_c on tbl_c(insert_time);
模拟数据量，tbl_c初始1300万数据，每天50万。
SQL> select trunc(insert_time), count(*) from tbl_c group by trunc(insert_time) order by trunc(insert_time) desc;
TRUNC(INS COUNT(*)
--------- ----------
06-MAY-17 500000
05-MAY-17 500000
04-MAY-17 500000
03-MAY-17 500000
02-MAY-17 500000
01-MAY-17 500000
30-APR-17 500000
29-APR-17 500000
28-APR-17 500000
27-APR-17 500000
26-APR-17 500000
25-APR-17 500000
24-APR-17 500000
23-APR-17 500000
22-APR-17 500000
21-APR-17 500000
20-APR-17 500000
19-APR-17 500000
18-APR-17 500000
17-APR-17 500000
16-APR-17 500000
15-APR-17 500000
14-APR-17 500000
13-APR-17 500000
12-APR-17 500000
11-APR-17 500000
26 rows selected.
删除2天100万数据，采用如下三种方式：
1. 按照insert_time时间删除，
create or replace procedure clear_1(i_time in number) as
begin
while true loop
execute immediate 'delete from tbl_c where insert_time < trunc(SYSDATE) - :1 and rownum <= 10000和50000两种' using i_time;
if sql%notfound then
exit;
end if;
commit;
end loop;
exception
when others then
commit;
end;
/
2. 使用游标、bulk和for删除，
create or replace procedure clear_2(i_time in number) as
MAX_ROW_SIZE constant pls_integer := 10000万和50000万两种;
row_id_table dbms_sql.urowid_table;
cursor cur is
select rowid from tbl_c where insert_time < trunc(sysdate) - i_time;
begin
open cur;
loop
fetch cur bulk collect into row_id_table limit MAX_ROW_SIZE;
for i in 1 .. row_id_table.count loop
execute immediate 'delete from tbl_c where rowid = :1' using row_id_table(i);
end loop;
commit;
exit when row_id_table.count < MAX_ROW_SIZE;
end loop;
commit;
close cur;
exception
when others then
commit;
if (cur%isopen) then
close cur;
end if;
end;
/
3. 使用游标、bulk和forall删除，
create or replace procedure clear_3(i_time in number) as
MAX_ROW_SIZE constant pls_integer := 10000万和50000两种;
row_id_table dbms_sql.urowid_table;
cursor cur is
select rowid from tbl_c where insert_time < trunc(sysdate) - i_time;
begin
open cur;
loop
fetch cur bulk collect into row_id_table
limit MAX_ROW_SIZE;
forall i in 1 .. row_id_table.count
execute immediate 'delete from tbl_c where rowid = :1' using row_id_table(i);
commit;
exit when row_id_table.count < MAX_ROW_SIZE;
end loop;
commit;
close cur;
exception
when others then
commit;
if (cur%isopen) then
close cur;
end if;
end;
/
insert_time有索引和无索引两种，
create index idx on tbl_c(insert_time):
测试：
1. insert_time无索引，
(1) clear_1
一次删除1万，00:01:16.31
一次删除5万，00:00:26.98
(2) clear_2
一次删除1万，00:00:40.50
一次删除5万，00:00:39.80
(3) clear_3
一次删除1万，00:00:21.73
一次删除5万，00:00:22.24
2. insert_time有索引，
(1) clear_1
一次删除5万，00:00:35.92
(2) clear_2
一次删除5万，00:00:33.24
(3) clear_3
一次删除5万，00:00:37.40
可以得出结论，
1. 若delete语句不能使用索引，则推荐clear_3>clear_2>clear_1，且一次批量处理数据量大小对性能无显著影响，若必须使用clear_1，则建议一次批量处理数据要大，同时注意UNDO空间的使用。
2. 若delete语句可以使用索引，则三者相近，我猜可能执行存储过程，需要删除的数据量越大，则clear_1>clear_2/clear_3。此处clear_3这种用法相当于批量发送SQL，比clear_2慢，不知道是否和我的环境、删除数据量等有关？
我理解删除100万数据，一次删除1万，
clear_1，执行100次delete语句。
clear_2，执行一次select语句，100次delete语句，但PLSQL向SQL引擎发送100次。
clear_3，执行一次select语句，100次delete语句，但PLSQL向SQL引擎发送1次。
不知道以上理解，和测试结论，是否有纰漏？谢谢各位指点！