为什么对于具有父子约束表的大型数据集，PL/SQL Bulk DML 运行速度会变慢？

Question

我一直试图弄清楚为什么这个 PL/SQL 清除脚本对于记录表有几十万或更多记录的数据集运行缓慢。在脚本执行之前，记录表的某个子集被标记为要清除 - 大约 75%。

是什么导致删除 Record_Part 表比其他表花费更长的时间？是因为它位于3表父子层次结构的中间吗？我是否在索引或约束方面遗漏了一些知识？我可以做什么来加速这个定期清除过程？

这是一个 Oracle 10g 数据库。

预先感谢您阅读我的问题。

架构（部分）：

• Record表是父表
• Record_Part表是Record的子表（Record有许多Record_Part）
• Record_Person是Record_Part的子表（Record_Part有许多Record_Person）
• 典型比例为1:7 :9 (record:record_part:record_person)

记录

• PK - sysid
• 物理 ID
• 待处理
• purge_in_progress

Record_Part

• PK - Part_pk
• FK - record_sysid

Record_Person

• PK - sysid
• FK - Part_pk

运行时

50000 个记录条目

• record_person forall 在 1:40 分钟内完成
• record_part forall 在 1 分 20 分钟内完成
• record forall 在 10 秒内完成

300000 个记录条目

• record_person forall 在 9 分钟内完成
• record_part forall 在 2 小时内完成
• record forall 在 20 分钟内完成

2000000 个记录条目

• record_person forall 在 1 内完成 小时
• record_part forall 在 13 小时内完成 小时 (!)
• 记录在 8 分钟内完成

索引和约束 DDL

alter table Record add constraint record_REC_PK primary key (SYSID) using index tablespace DB_INDEX1;
alter table Record_Part add constraint RECPART_REC_PK primary key (Part_PK) using index tablespace DB_INDEX1;
alter table Record_Part add constraint RECPART_FK foreign key (RECORD_SYSID) references record (SYSID);
alter table Record_Person add constraint RECPERSON_REC_PK primary key (SYSID) using index tablespace DB_INDEX1;
alter table Record_Person add constraint RECPERSON_FK foreign key (Part_PK) references Record_Part (Part_PK);

CREATE INDEX REC_PURGE_IDX ON record (PURGE_IN_PROGRESS);
CREATE INDEX REC_PHYSID_IDX ON record (PHYSICALID);
CREATE INDEX REC_PENDING_IDX ON record (PENDING);
CREATE INDEX RECPART_RECORD_SYSID_IDX ON Record_Part (RECORD_SYSID);
CREATE INDEX RECPERSON_PARTPK_IDX on Record_Person (PART_PK);

脚本： （下面的脚本中省略了时间戳打印）

DECLARE

TYPE sSysid IS TABLE OF record.sysid%TYPE
    INDEX BY PLS_INTEGER;

TYPE physicalid IS TABLE OF record.physicalid%TYPE
    INDEX BY PLS_INTEGER;    

l_sid sSysid;
l_physicalid physicalid;

BEGIN
    SELECT sysid, physicalid
    BULK COLLECT INTO l_sid, l_physicalid
        FROM record
        where purge_in_progress = 1;

FORALL i IN l_sid.FIRST .. l_sid.LAST
    delete from record_person where Part_pk like concat(l_sid(i), '%') or Part_pk like concat(l_physicalid(i), '%');

commit;

FORALL i IN l_sid.FIRST .. l_sid.LAST
    delete from record_Part where record_sysid = l_sid(i);

commit;

FORALL i IN l_sid.FIRST .. l_sid.LAST
    delete from record where sysid = l_sid(i);

END;
/

commit;

Answer 1

检查此线程的第一个回复。正如 Justin 指出的，您需要使用 limit 子句来获取固定数量的记录（通常使用 100 条，您可以对其进行参数化，看看什么适合您的情况）。

http://asktom.oracle。 com/pls/asktom/f?p=100:11:0::::p11_question_id:5918938803188

Answer 2

您可以在测试环境中禁用 FK 约束来看看是否有帮助？

另一种可能性是将 FK 约束重新创建为可延迟的，并在脚本开始时延迟约束，例如：

alter table Record_Part 
      add constraint RECPART_FK foreign key (RECORD_SYSID) 
                                references record (SYSID) DEFERRABLE;
alter table Record_Person 
      add constraint RECPERSON_FK foreign key (Part_PK) 
                                  references Record_Part (Part_PK) DEFERRABLE;

SET CONSTRAINTS ALL DEFERRED;
...run your purge
SET CONSTRAINTS ALL IMMEDIATE;

请注意，任何 COMMIT 将导致立即设置约束。每次提交后，您必须重新发出第一个设置约束语句。

限制因素将是我的第一个怀疑。