在包含多个关系的表中删除100万行的最佳方法

Question

我有一组表，其中主表有150万行。其中一个子级也接近100万行。

主表的数据也必须复制到历史表中。我编写了一个PLSQL脚本，首先删除子行和子行，但这个过程太长了，大约需要12个小时。

我正在使用批量收集来删除数据。

该脚本将由Java调度程序在每周一凌晨3点执行。

我假设在第一次批量删除(有一百万行)之后，这个过程会很快，因为数据库每周只会增长到30k个寄存器。

解决此问题的最佳解决方案是什么？

Answer 1

通常，从表中“删除”大量行的最快方法是将它们存储在一个临时表中，截断该表，然后重新插入它们：

create table tempt as
    select *
    from t
    where . . .;

truncate table t;

insert into t
    select *
    from tempt;

这只适用于一个表，并且它不会级联删除。根据触发器的设置方式，数据可能会发生变化(例如插入时间)。你的问题没有提供足够的信息。

但是，从理论上讲，您可以在子表上继续此过程：

create table tempchild as
    select *
    from child
    where child.parentid in (select id from t);

truncate table child;

insert into child
    select *
    from tempchild;

这是一个快速实现这一目标的方法的概述。如果触发器和约束可能会影响不同表中记录之间的关系，情况就会变得更加复杂。

Answer 2

如果不解释您试图从中删除的表、与其相关的表以及所有这些表上的索引，就不可能说出为什么这些删除花费了这么长时间。一种想法是，如果您有一个从一个表到另一个表的外键，那么外键关系中涉及的字段应该在两个表上都建立索引。例如，假设您有以下表：

PARENT_TABLE (1,000,000 rows)
  ID_PARENT       NUMBER PRIMARY KEY
  SOMETHING       VARCHAR2(10)
  SOMETHING_ELSE  NUMBER
  VALUE_FIELD     VARCHAR2(20) REFERENCES VALID_VALUES(VALUE_FIELD)
  ID_XXX_TABLE    NUMBER REFERENCES XXX_TABLE (ID_XXX_TABLE)

CHILD_TABLE (2,000,000 rows)
  ID_CHILD        NUMBER PRIMARY KEY
  ID_PARENT       NUMBER REFERENCES PARENT_TABLE(ID_PARENT) CASCADE DELETES
  BLAH            VARCHAR2(15)
  BLAH_BLAH       VARCHAR2(100)
  VALUE_FIELD     VARCHAR2(20) REFERENCES VALID_VALUES(VALUE_FIELD)

XXX_TABLE (100,000 rows)
  ID_XXX_TABLE    NUMBER PRIMARY KEY
  FUBAR           NUMBER
  FOOBAR          NUMBER
  VALUE_FIELD     VARCHAR2(20) REFERENCES VALID_VALUES(VALUE_FIELD)

VALID_VALUES (25 rows)
  VALUE_FIELD     VARCHAR2(20) PRIMARY KEY
  GOOD_BAD_UGLY   CHAR(1) CHECK(GOOD_BAD_UGLY IN ('G', 'B', 'U')

在这种情况下，应创建以下索引：

PK_PARENT_TABLE ON PARENT_TABLE (ID_PARENT)
PARENT_TABLE_1  ON PARENT_TABLE (VALUE_FIELD)
PARENT_TABLE_2  ON PARENT_TABLE (ID_XXX_TABLE)

PK_CHILD_TABLE  ON CHILD_TABLE (ID_CHILD)
CHILD_TABLE_1   ON CHILD_TABLE (ID_PARENT)
CHILD_TABLE_2   ON CHILD_TABLE (VALUE_FIELD)

PK_XXX_TABLE    ON XXX_TABLE (ID_XXX_TABLE)
XXX_FIELD_1     ON XXX_TABLE (VALUE_FIELD)

PK_VALID_VALUES ON VALID_VALUES (VALUE_FIELD)

为了解释为什么需要创建这些索引，让我们考虑一下当您想要从这些表中删除一行时会发生什么：

PARENT_TABLE

DELETE FROM PARENT_TABLE WHERE ID_PARENT = 123

执行此操作时，数据库需要查找引用被删除行的每一行。只有一个表具有引用PARENT_TABLE的外键约束，那就是CHILD_TABLE。因此，数据库必须执行等同于SELECT * FROM CHILD_TABLE WHERE ID_PARENT = 123的操作。现在，这条语句总是可以执行的-但是如果CHILD_TABLE.ID_PARENT没有被索引，就可以保证计划将是FULL TABLE SCAN的，并且CHILD_TABLE中的每一行都必须被读取。如果CHILD_TABLE有很多行，这会很慢。另一方面，如果CHILD_TABLE在ID_PARENT上有一个索引，那么它应该是一个直接的索引查找，通常是相当快的。

VALID_VALUES

DELETE FROM VALID_VALUES WHERE VALUE_FIELD = 'ZORTNOBBLE'

看起来很简单-只需从VALID_VALUES中删除一行，它只有25行。应该很快，对吧？Hmmmm...maybe不是。有三个引用VALID_VALUES.VALUE_FIELD的外键约束-如果其中任何一个引用的表在VALUE_FIELD上缺少索引，它将强制读取该表中的每一行，并检查要从VALID_VALUES中删除的值是否存在。这很可能是一个非常慢的操作。

因此，正如我所说的，要确保外键约束两侧的表在每个外键中的所有字段上都有一个索引。你可能会发现这将极大地改善你的情况。

如果您可以编辑您的问题并包括所涉及的表的定义，包括这些表上存在的所有外键约束和所有索引，则可能有人能够提供更具体的建议。

祝你好运。

Answer 3

我做了一些测试，发现表本身非常慢。我不能在我正在测试的环境中删除索引，但我认为索引导致了性能问题(删除300条记录花费了18秒)。

我给程序增加了一个超时，所以它每天只需要花费4个小时来删除表中的数据，它可以暂时解决我的问题。