Why is PL/SQL Bulk DML running slowing for large datasets with parent-child constrained tables?

I have been trying to figure out why this PL/SQL purge script runs slowly for datasets where the record table has a a few hundred thousand or more records. Prior to script execution, a certain subset of the Record table is flagged for purging - about 75%.

What causes the deletion of the Record_Part table to take so much longer than the other tables? Is it because it is in the middle of the 3-table parent-child hierarchy? Am I missing some piece of knowledge here in terms of indexes or constraints? What can I do to speed up this periodic purging process?

This is an Oracle 10g database.

Thanks in advance for reading my question.

Schema (Partial):

• Record table is the parent table
• Record_开发者_如何学CPart table is the child of Record (Record has many Record_Part)
• Record_Person is the child of Record_Part (Record_Part has many Record_Person)
• The typical ratio is 1:7:9 (record:record_part:record_person)

Record

• PK - sysid
• physicalid
• pending
• purge_in_progress

Record_Part

• PK - Part_pk
• FK - record_sysid

Record_Person

• PK - sysid
• FK - Part_pk

Runtimes

50000 record entries

• record_person forall completes in 1:40 min
• record_part forall completes in 1:20 min
• record forall completes in 10 seconds

300000 record entries

• record_person forall completes in 9 min
• record_part forall completes in 2 hours
• record forall completes in 20 minutes

2000000 record entries

• record_person forall completes in 1 hour
• record_part forall completes in 13 hours (!)
• record forall completes in 8 minutes

Index and Constraint 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);

Script: (timestamp printing omitted from script below)

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;

Check the first reply on this thread. As Justin pointed out, you need to use the limit clause to fetch a fixed number of records (100 is the generally used, you can parameterize this and see what works for your situation).

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

Can you disable the FK constraints in a test environment to see if that helps?

Another possibility is to re-create the FK constraints as deferrable, and defer the constraints at the start of the script, e.g.:

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;

Note that any COMMIT will cause the constraints to be set immediate. You will have to reissue the first set constraints statement after each commit.

The constraints would be my first suspect here.