如何加快执行delete语句的速度
答案:2 悬赏:80
解决时间 2021-02-22 09:43
- 提问者网友:单纯说谎家
- 2021-02-21 20:51
如何加快执行delete语句的速度
最佳答案
- 二级知识专家网友:丢不掉的轻狂
- 2021-02-21 22:26
分批commit
CREATE OR REPLACe PROCEDURE BATCH_DELETE(DML VARCHAR2, ONCE_COUNT NUMBER) IS
DELETED_COUNT NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('PROCESS : ' || DML);
DBMS_OUTPUT.PUT_LINE('BEGIN : ' || TO_CHAr(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
LOOP
EXECUTE IMMEDIATE DML || ' AND ROWNUM <= :CNT' USING ONCE_COUNT;
IF SQL%NOTFOUND THEN
EXIT;
END IF;
DELETED_COUNT:= DELETED_COUNT + SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(' DELETED ' || DELETED_COUNT || ' DATA.');
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('END : ' || TO_CHAr(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
END BATCH_DELETE;
exec batch_delete('DELETE FROM TBL_AAAAA WHERe BBB_DT < ''20080901''', 10000);
CREATE OR REPLACe PROCEDURE BATCH_DELETE(DML VARCHAR2, ONCE_COUNT NUMBER) IS
DELETED_COUNT NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('PROCESS : ' || DML);
DBMS_OUTPUT.PUT_LINE('BEGIN : ' || TO_CHAr(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
LOOP
EXECUTE IMMEDIATE DML || ' AND ROWNUM <= :CNT' USING ONCE_COUNT;
IF SQL%NOTFOUND THEN
EXIT;
END IF;
DELETED_COUNT:= DELETED_COUNT + SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(' DELETED ' || DELETED_COUNT || ' DATA.');
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('END : ' || TO_CHAr(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
END BATCH_DELETE;
exec batch_delete('DELETE FROM TBL_AAAAA WHERe BBB_DT < ''20080901''', 10000);
全部回答
- 1楼网友:一只傻青衣
- 2021-02-21 23:22
sql%rowcount就行。
其中的sql是oracle的内部游标,rowcount的意思是之前的dml sql语句影响的多少行数据。
如果是delete操作rowcount就是delete的数据,如果是insert,就是插入的数量......
你可以在过程中加入这句去测试下:
dbms_output.put_line(to_char(sql%rowcount));
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯