删除231万数据2-3分钟的过程_删除31w数据要多久-优快云博客

这个语句根据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;