写一个循环删除的过程。 createorreplaceproceduredelBigTab(p_TableNameinvarchar2,p_Conditioninvarchar2,p_Countinvarchar2) as pragmaautonomous_transaction; n_deletenumber:=0; begin while1=1loop EXECUTEIMMEDIATE 'deletefrom'||p_TableName||'where'||p_Condition||'andrownum<=:10000' USINGp_Count; ifSQL%NOTFOUNDthen exit; else n_delete:=n_delete+SQL%ROWCOUNT; endif; commit; endloop; commit; DBMS_OUTPUT.PUT_LINE('Finished!'); DBMS_OUTPUT.PUT_LINE('Totally'||to_char(n_delete)||'recordsdeleted!'); enddelBigTab; 调用: SQL>settimingon SQL>execdelBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID<11100000','10000'); PL/SQLproceduresuccessfullycompleted. Elapsed:00:00:18.54 方法虽好,但我应用在一个亿级数据库时还是觉得慢得不行。就算删一点点数据也觉得好象挺慢的。
(编辑:商洛站长网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|