<script language=javascript src="/plus/ad_js.php?aid=5"></script> <script type=text/javascript>
</script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type=text/javascript> </script> 量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。
下面是我的删除过程,我的数据表可以通过主键删除,测试过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; |