- 论坛徽章:
- 2
|
你这个最好建立分区表,但现在要删除
你一次删除1800W很慢,但一次删除1000,1W应该很快的
给你个存储过程,自己改下,我几亿的数据都是用这个删的
- CREATE OR REPLACE PROCEDURE "P_DELETE_SESSIOIN" (P_OUT_SQLCODE OUT VARCHAR2, P_OUT_ERRMSG OUT VARCHAR2) AS
- pragma autonomous_transaction;
- V_NAME VARCHAR(100) := 'P_DELETE_SESSIOIN';
- V_START DATE;
- ROWNUM NUMBER(10):=0;
- V_ROW_COUNT NUMBER(10):=0;
- n_delete number:=0;
- -- p_TableName VARCHAR2(20):=ODS_TBL_SESSION;
- -- p_Condition VARCHAR2(40) :=CREATETIME<TRUNC(SYSDATE);
- p_Count VARCHAR2(20):='20000';
- BEGIN
- P_OUT_SQLCODE :='0';
- P_OUT_ERRMSG :='OK';
- V_START := SYSDATE;
- while 1=1 loop
- EXECUTE IMMEDIATE
- --这里一次删除2W条
- 'delete from ODS_TBL_SESSION where CREATETIME<TRUNC(SYSDATE)'||' 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!');
- V_ROW_COUNT:=n_delete;
- ROWNUM :=ROWNUM+V_ROW_COUNT;
- EXCEPTION
- WHEN OTHERS THEN
- P_OUT_SQLCODE :=SQLCODE;
- P_OUT_ERRMSG :='FAILED';
- ROLLBACK;
- END P_DELETE_SESSIOIN;
复制代码 |
|