删除231万数据2-3分钟的过程-优快云博客

本文介绍两种批量删除Oracle数据库中大数据表的方法。一种是通过ROWID定位记录进行分批删除；另一种是利用ROWNUM限制每次删除的数据量。通过对比测试，展示了不同方法在效率上的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这个语句根据ROWID
 






create or replace procedure delBigTab 
( 
p_TableName in varchar2, 
p_Condition in VARCHAR2 
) 
AS 
type mycur is ref cursor; 
v_cur mycur; 
v_cur_sql VARCHAR2(2000); 
l_sql VARCHAR2(2000); 
pragma autonomous_transaction; 
n_delete number:=0; 
Type v_rowid is table of varchar2(100) index by binary_integer; 
var_rowid v_rowid; 
BEGIN 
v_cur_sql :='select rowid from '||p_TableName||' where '||p_Condition||' order by rowid' ; 
OPEN v_cur FOR v_cur_sql; 
LOOP 
FETCH v_cur BULK COLLECT 
INTO var_rowid LIMIT 20000 ; 
FORALL i IN 1 .. var_rowid.count 
/* DELETE FROM datasync_prc.ax_log_mail WHERE ROWID=var_rowid(i);*/ 
EXECUTE IMMEDIATE 'delete from '||p_TableName||' where rowid=:1' USING var_rowid(i); 
COMMIT; 
EXIT WHEN v_cur%NOTFOUND OR v_cur%NOTFOUND IS NULL; 
END LOOP; 
CLOSE v_cur; 
end;



第二个根据rownum 限制数据量来删除
create or replace procedure delete_big_table(pi_table_name in varchar2,pi_condition in varchar2) is 
pragma autonomous_transaction; 
lv_delete_sql varchar2(2000); 
lv_select_sql varchar2(2000); 
ln_delete_num number; 
type mycur is ref cursor; 
v_select_cur mycur; 
v_delete_cur mycur; 
begin 
ln_delete_num:=0; 
lv_select_sql:=' select count(1) from '||trim(pi_table_name)||' where '||pi_condition; 
open v_select_cur for lv_select_sql; 
fetch v_select_cur into ln_delete_num; 
close v_select_cur; 
lv_delete_sql:=' DELETE '||trim(pi_table_name)||' where rownum<=:num_count and '||pi_condition; 
while 1=1 loop 
execute immediate lv_delete_sql using 2000; 
ln_delete_num:=ln_delete_num-2000; 
commit; 
EXIT WHEN ln_delete_num <=0; 
end loop; 
end delete_big_table;



测试删除


TABLE_NAME MAX(NUM_ROWS)
 
TEMP_WUND_ACTION 2328150 


把

TEMP_WUND_ACTION 复制下面两张表 


 analyze table TEMP_WUND_ACTION_01 compute statistics;
 analyze table TEMP_WUND_ACTION_02 compute statistics;
并做分析


TABLE_NAME MAX(NUM_ROWS)



TEMP_WUND_ACTION_02 2315417 
TEMP_WUND_ACTION_01 2315417



分别执行


execute delete_big_table('TEMP_WUND_ACTION_01','1=1');

为166.625秒



execute delBigTab('TEMP_WUND_ACTION_02','1=1');为116.141秒