Geek asks and answers

Why does PL / SQL Bulk DML work with slowdown for large datasets with tables with limited parent flow?

I tried to find out why this PL / SQL script cleanup script is slow for datasets where the record table has several hundred thousand or more records. Before running the script, a specific subset of the records table is marked for cleaning - about 75%.

What causes the removal of the Record_Part table takes much longer than other tables? Is it because it is in the middle of a parent-child hierarchy with three tables? Am I missing some of the knowledge here in terms of indexes or restrictions? What can I do to speed up this periodic cleaning process?

This is an Oracle 10g database.

Thanks in advance for reading my question.

Scheme (partial):

  • The record table is the parent table.
  • The Record_Part table is a child of a record (there are many Record_Part records in a record)
  • Record_Person is a child of Record_Part (Record_Part has many Record_Person)
  • Typical ratio: 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

50,000 record entries

  • record_person forall completes in 1:40 min.
  • record_part forall ends in 1:20 min.
  • recording completed in 10 seconds

300,000 record entries

  • record_person forall completes after 9 minutes
  • record_part ends in 2 hours
  • recording completed in 20 minutes

2,000,000 record entries

  • record_person forall ends at 1 o'clock
  • record_part forall ends at 13 hours (!)
  • recording completed in 8 minutes

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: ( script)

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;
+3
2

FK , , ?

- FK script, :

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 . set constraints .

.

+2

More articles:


All Articles