如何更快地删除？

Question

如何更快地删除？

databaseperformanceoracleplsql

6

我有一个包含25亿条记录的数据库表。

其中有1100万条重复的记录。

删除这1100万条记录最快的方法是什么？

- Chattz

仅为展示系统性能，获取重复行计数的查询花费了1小时40分钟。 - Chattz

我认为原帖作者已经删除了他的账户。 - Shimmy Weitzhandler

谢谢大家！我需要将唯一记录复制到一个表中，清空原始表并将唯一数据复制回去。 - Chattz

5个回答

3

DELETE
FROM    mytable
WHERE   rowid IN
        (
        SELECT  rowid
        FROM    (
                SELECT  rowid, ROW_NUMBER() OVER (ORDER BY dupfield) rn
                FROM    mytable r
                )
        WHERE   rn > 1
        )

或者甚至是这样：

DELETE
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.dup_field = mo.dup_field
                AND mi.rowid <> mo.rowid
        )

这两个查询都将使用相当高效的HASH SEMI JOIN，如果dup_field上没有索引，则后者将更快。

你可能会想要复制这些行，但请注意，复制2G行时会产生比删除11M行时更多的REDO和UNDO信息。

- Quassnoi

1

当表的大小达到25亿时，像这样的更新操作性能如何？ - Mr. Shiny and New 安宇

我有种感觉这个查询速度很慢，但可以实现OP需要的功能。能否将其重写为连接？ - Manuel Ferreria

如果dupfield上没有索引，将会进行排序，这可能需要很长时间。在rowid上的连接将是HASH SEMI JOIN，对于11M行数据，在2G上只需要几分钟。删除本身也需要数十分钟，主要是为了生成REDO和UNDO。 - Quassnoi

@Manuel：Oracle 足够聪明，可以将其重写为更高效的 HASH SEMI JOIN。 - Quassnoi

2

无论是删除现有行还是创建一个适当的新表并且删除旧表，哪个更快取决于很多因素。1100万行是很多，但它只占表中总行数的0.5％。重新创建和删除可能比删除更慢，这取决于源表上存在多少索引，以及需要删除的行在数据页上的位置。

然后还有一个问题，源表是否处于运行状态。如果在进行清理时有插入和更新操作，则复制和删除将无法正常工作，需要编写大量额外的代码来在事后同步表格。

最后，为什么这个操作需要“快”？是因为系统需要在此过程中离线吗？您可以编写一个过程，在系统在线时删除重复项，但不会影响系统的其他方面，例如影响撤消。我们过去通过先编写一个查询，将要删除的行的主键收集到第二个表中来解决这个问题，如下所示：

  INSERT
    INTO RowsToDeleteTable
  SELECT PKColumn
    FROM SourceTable
   WHERE <conditions used to find rows to remove>

CREATE UNIQUE INDEX PK_RowsToDelete ON RowsToDeleteTable (PKColumn);

然后我们有一个 PL/SQL 块，可以像这样循环遍历游标中的行：

BEGIN
  FOR theRow IN (SELECT PKColumn FROM RowsToDeleteTable ORDER BY 1) LOOP
    <delete source table for theRow.PKColumn)
    <optionally wait a bit>
    commit;
  END LOOP;
END;

或者做类似这样的事情：

BEGIN
  FOR theRow IN (SELECT MIN(PKColumn) FROM RowsToDeleteTable ) LOOP
    <delete source table for theRow.PKColumn)
    <optionally wait a bit>
    DELETE RowsToDeleteTable
     WHERE PKColumn = theRow.PKColumn;
    commit;
  END LOOP;
END;

循环和"SELECT MAX"显然不够高效，但它的优点是可以让您跟踪删除操作的进度。我们在循环中加入了一些等待代码，以便控制收割操作的强度。

RowsToDeleteTable的初始创建非常快，而且您可以让该过程花费任意长的时间。在这种情况下，由于您只删除了总数据的一小部分，因此删除操作留下的"空洞"不会太严重。

- Steve Broberg

1

首先，在定义和包含重复值的列上放置索引，

然后，假设表具有主键（PK），

  Delete Table T Where PK <> 
        (Select Min(PK) From Table
         Where ColA = T.ColA
           ...  for each column in set defined above
           And ColB = T.ColB)

注意：也可以使用Max（PK），你所做的就是识别每组重复记录中不删除的单个记录

编辑：为了消除对事务日志和UNDO分区的广泛使用，您可以将重复值存储在临时表中，然后在单个事务内删除每对重复项...

假设只有一列（称为ColA，一个数字）定义了重复项...

   Create Table Dupes (ColA Number)
   Insert Dupes(ColA)
   Select Distinct ColA
   From Table
   Group By ColA
   Having Count(*) > 1

   recordExists Number := 0 ;
   ColAValue Number;
   Select Case When Exists (Select Count(*) From Dupes)
   Then 1 Else 0 End Into recordExists From Dual;


   While recordExists = 1 
      Loop 
         Select (Select Max(ColA) From Dupes) 
         Into ColAValue From Dual;
         Begin Transaction
            Delete Table T
            Where ColA = ColAValue
               And pk <> (Select Min(Pk) From Table 
                          Where ColA = ColAValue);
            Delete Dupes Where ColA = ColAValue;
         Commit Transaction;
         Select Case When Exists (Select Count(*) From Dupes)
         Then 1 Else 0 End Into recordExists From Dual;
      End Loop;

未经测试，因此语法可能需要调整...

- Charles Bretana

0

如果您确定不会改变数据完整性（引用完整性），禁用约束（索引、其他约束），执行删除，然后启用约束。您必须先尝试一下，看看在启用时刷新索引是否比启用它们进行删除所需的时间更少。

一些查询优化也可能有所帮助，但如果不知道更多细节，我们就只能从理论上讨论。

- Cătălin Pitiș

1

不要删除用于查找重复项的列上的索引，对2,500,000,000行进行重复的全表扫描将非常非常非常慢。 - Richard

如果没有索引，它不会执行重复的表扫描，而是会执行哈希半连接。 - Quassnoi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- skaffman · Accepted Answer

删除多个重复记录中的一个是一项棘手的任务，如果有很多记录，你就会遇到问题。

其中一种选择是反其道而行之，将要保留的记录复制到一个新表中。您可以使用CREATE TABLE AS SELECT DISTINCT ... NOLOGGING语法，它将复制您的去重记录而不使用事务日志，这样速度更快。一旦新表填充完毕，删除/重命名旧表，并将新表重命名为原来的表名。

请参见http://www.databasejournal.com/features/oracle/article.php/3631361/Managing-Tables-Logging-versus-Nologging.htm

哦，还要记得在新表上打上唯一索引，以防止再次出现此类情况。

故事的寓意是... 永远不要使用DELETE来删除大量记录，因为它需要将所有已删除的记录存储在重做日志中，速度非常慢。要么复制并切换，要么使用TRUNCATE。