删除231万数据2-3分钟的过程

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

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

<p><span style="font-size: small;"><span style="font-family: courier new,courier;">测试删除</span>
</span>
</p>
<p><span style="font-size: small;"><span style="font-family: courier new,courier;"><strong>TABLE_NAME MAX(NUM_ROWS)</strong>
<br>
TEMP_WUND_ACTION 2328150<br></span>
</span>
</p>
<p><span style="font-size: small;"><span style="font-family: courier new,courier;">把</span>
</span>
<span style="font-size: small;"><span style="font-family: courier new,courier;">TEMP_WUND_ACTION 复制下面两张表 </span>
</span>
</p>
<p> analyze table TEMP_WUND_ACTION_01 compute statistics;</p>
<p> analyze table TEMP_WUND_ACTION_02 compute statistics;</p>
<p><span style="font-size: small;"><span style="font-family: courier new,courier;">并做分析</span>
</span>
</p>
<p><strong><span style="font-size: small;"><span style="font-family: courier new,courier;">TABLE_NAME MAX(NUM_ROWS)</span>
</span>
</strong>
</p>
<p><span style="font-size: small;"><span style="font-family: courier new,courier;">TEMP_WUND_ACTION_02 2315417<br>
TEMP_WUND_ACTION_01 2315417</span>
</span>
</p>

<p><span style="font-size: small;"><span style="font-family: courier new,courier;">分别执行</span>
</span>
</p>
<p><span style="font-size: small;"><span style="font-family: courier new,courier;">execute delete_big_table('TEMP_WUND_ACTION_01','1=1');</span>
</span>
<span style="color: #ff0000;"><span style="font-size: small;"><span style="font-family: courier new,courier;">为166.625秒</span>
</span>
</span>
</p>
<p><span style="font-size: small;"><span style="font-family: courier new,courier;">execute delBigTab('TEMP_WUND_ACTION_02','1=1');<span style="color: #0000ff;">为116.141秒</span>
<br></span>
</span>
</p>