title: Why is PL/SQL Bulk DML running slowing for large datasets with parent-child constrained tables?
body: <p>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%.</p>
  
  <p>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?  </p>
  
  <p>This is an Oracle 10g database.</p>
  
  <p>Thanks in advance for reading my question.</p>
  
  <p><strong>Schema (Partial):</strong></p>
  
  <ul>
  <li>Record table is the parent table</li>
  <li>Record_Part table is the child of Record (Record has many Record_Part)</li>
  <li>Record_Person is the child of Record_Part (Record_Part has many Record_Person)</li>
  <li>The typical ratio is 1:7:9 (record:record_part:record_person)</li>
  </ul>
  
  <p>Record</p>
  
  <ul>
  <li>PK - sysid</li>
  <li>physicalid</li>
  <li>pending</li>
  <li>purge_in_progress</li>
  </ul>
  
  <p>Record_Part</p>
  
  <ul>
  <li>PK - Part_pk</li>
  <li>FK - record_sysid</li>
  </ul>
  
  <p>Record_Person</p>
  
  <ul>
  <li>PK - sysid</li>
  <li>FK - Part_pk</li>
  </ul>
  
  <p><strong>Runtimes</strong></p>
  
  <p>50000 record entries</p>
  
  <ul>
  <li>record_person forall completes in 1:40 min</li>
  <li>record_part forall completes in 1:20 min</li>
  <li>record forall completes in 10 seconds</li>
  </ul>
  
  <p>300000 record entries</p>
  
  <ul>
  <li>record_person forall completes in 9 min</li>
  <li>record_part forall completes in 2 hours</li>
  <li>record forall completes in 20 minutes     </li>
  </ul>
  
  <p>2000000 record entries</p>
  
  <ul>
  <li>record_person forall completes in 1
  hour</li>
  <li>record_part forall completes in 13
  hours (!)</li>
  <li>record forall completes in 8 minutes</li>
  </ul>
  
  <p><strong>Index and Constraint DDL</strong></p>
  
  <pre><code>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);
  </code></pre>
  
  <p><strong>Script:</strong>
  (timestamp printing omitted from script below)</p>
  
  <pre><code>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;
  </code></pre>