从Oracle中的大表中删除数据需要更长的执行时间

Question

我遇到一种情况，需要从Oracle数据库中删除Log表中的数据。该表包含大约3159037行CLOB内容，现在作为一项维护活动，我需要通过保留当前日期后的最后6个月来清除旧数据。我设计了PL/SQL程序来实现这一点，但在测试过程中，我发现即使删除1000条记录，该过程也需要大约22秒才能完成执行。现在假设我们有超过100000条记录要删除，这将需要大量时间才能完成执行。

有人能提出解决这个问题的更好方法吗。

以下是我当前使用FORALL和BULK COLLECT功能获得性能优势的脚本。

CREATE OR replace PROCEDURE Integration_data_purge
authid current_user
AS
c_limit CONSTANT PLS_INTEGER DEFAULT 100;
CURSOR int_cur IS
SELECT a.ROWID AS a_rowid,
b.ROWID AS b_rowid
FROM   integration_log a,
integration_errors b
WHERE  a.error_log_id = b.error_rec_id(+)
AND Trunc(a.insert_date) < Trunc(SYSDATE) - 180;
TYPE int_aat
IS TABLE OF int_cur%ROWTYPE INDEX BY PLS_INTEGER;
l_integration_log INT_AAT;
--l_count     INTEGER := 1;
start_time        NUMBER;
end_time          NUMBER;
BEGIN
start_time := dbms_utility.get_time;
OPEN int_cur;
LOOP
FETCH int_cur bulk collect INTO l_integration_log limit c_limit;
--DBMS_OUTPUT.put_line ('Retrieved in RUN '|| l_count ||' = ' ||
-- l_integration_log.COUNT);
EXIT WHEN l_integration_log.count = 0;
--if l_integration_log.count <> 0 then
forall indx IN 1 .. l_integration_log.count
DELETE FROM integration_log
WHERE  ROWID = L_integration_log(indx).a_rowid;
--DBMS_OUTPUT.put_line ('Total rows deleted from INTEGRATION_LOG = ' ||
--SQL%ROWCOUNT);
forall indx IN 1 .. l_integration_log.count
DELETE FROM integration_errors
WHERE  ROWID = L_integration_log(indx).b_rowid;
--DBMS_OUTPUT.put_line ('Total rows deleted from INTEGRATION_ERRORS = ' ||
--SQL%ROWCOUNT);
--l_count := l_count + 1;
--end if;
--commit;
END LOOP;
CLOSE int_cur;
end_time := dbms_utility.get_time;
dbms_output.Put_line('Execution Completed : '
|| To_char(( end_time - start_time ) / 100)
|| ' Seconds');
END;  

Answer 1

设计相当糟糕。试试这个

DELETE FROM integration_errors WHERE err_rec_id in (select error_log_id from integation_log where insert_date < sysdate - 180) ;
DELETE from integation_log where insert_date < sysdate - 180;

除非有基于函数的索引，否则不要使用trunc(insert_date)。删除数据前禁用外键约束，删除数据后再次启用。

考虑分区，在较新的Oracle中，您也可以通过ref键进行分区