在没有并行提示和批量收集的情况下删除数百万条记录

Question

我有一个表PROD_MAIN，它在一个数据库上有7.5亿条记录。数据库基础架构非常基础，没有任何RAC。它只是一个数据库。要求删除超过1年的记录。我编写了一个带有并行提示和批量收集的PL SQL代码。它需要很长的时间来执行。请找到下面的代码。

ALTER SESSION ENABLE PARALLEL DML;

DECLARE
TYPE TABLE_DELETE IS TABLE OF ROWID;
T_DELETE TABLE_DELETE;
CURSOR C_DELETE IS
SELECT /*+ PARALLEL(10) */ ROWID FROM PROD_MAIN WHERE RECORD_DATE < (TRUNC(SYSDATE) - 366);
L_DELETE_BUFFER PLS_INTEGER := 50000;
BEGIN
OPEN C_DELETE;
LOOP
FETCH C_DELETE BULK COLLECT
INTO T_DELETE LIMIT L_DELETE_BUFFER;
FORALL I IN 1..T_DELETE.COUNT
DELETE /*+ PARALLEL(10) */ PROD_MAIN WHERE ROWID = T_DELETE(I);
EXIT WHEN C_DELETE%NOTFOUND;
COMMIT;
END LOOP;
CLOSE C_DELETE;
COMMIT;
END;

ALTER SESSION DISABLE PARALLEL DML;

我还在桌子上做了NOLOGGING。我创建了索引并进行了统计数据收集，但性能并没有提高。那么，有没有其他方法可以让我在3-5小时内删除数百万条记录？

Answer 1

我通过创建一个具有与PROD_MAIN完全相同的表结构的临时表PROD_MAIN_TEMP解决了这个问题。创建后，我插入了我想要保留的数据。选择/*+ PARALLEL(10) */ * FROM PROD_MAIN WHERE RECORD_DATE < (TRUNC(SYSDATE) - 366)；删除主表PROD_MAIN并将临时表PROD_MAIN_TEMP重命名为PROD_MAIN。整个过程在3个小时内完成。

Answer 2

如果表是按日期分区的，您可以截断分区超过一年的时间(截断分区不会耗费时间，而不会降低表的性能)

如果它没有分区，我认为您可以做的最好的事情就是不要试图在一个事务中删除所有记录。尝试删除一些记录并将其放入循环中。例如，如果你想删除10.000条记录，你可以这样做：

DELETE FROM your_table WHERE your_conditions LIMIT 10.000 (MySQL)
DELETE FROM your_table WHERE your_conditions AND rownum <10000 (Oracle)

记住在完成后(或者甚至在删除之间交替)优化表，因为这会降低索引。

根据您的环境要求，您可以尝试的另一件事是创建一个空表副本，并执行INSERT from SELECT，在新表中插入您想要维护的所有行。之后，截断原始表，删除它，然后重命名新的表。

MyOriginalTable whit All Data
Create en Empty Copy: MyTemporalTable (without indexes)
Move valid data from MyOriginalTable to MyTemporalTable
Truncate and Drop MyOriginalTable
Create indexes in MyTemporalTable
Rename MyTemporalTable to MyORiginalTable

Answer 3

我认为问题在于:这个表是其他表的主表。

为了提高速度，请在其他表中禁用这些外键。然后删除行，然后启用索引。

但'Diego Sal Diaz‘的第三个解决方案将剩余行复制到临时表并重命名它也很好。