All,
i have 5 tables
Table A has 33K records
Table B has 35M records
Table C has 3M records
Table D has 13M records
Table E has 8M records
i need to delete all the E_ID from Table B / C / D / E which are present in the Table A.
i have foreign key relation between Table B and Table C / D / E on E_ID. Also i have created an index on the column E_ID on Table C / D / E
executed the below command
DELETE FROM table C WHERE E_ID in (SELECT E_ID FROM table A);
COMMIT;--completes in seconds
DELETE FROM table D WHERE E_ID in (SELECT E_ID FROM table A);
COMMIT;--completes in seconds
DELETE FROM table E WHERE E_ID in (SELECT E_ID FROM table A);
COMMIT;--completes in seconds
DELETE FROM table B WHERE E_ID in (SELECT E_ID FROM table A);
COMMIT;-- runs for hours and eventually becomes a long running query
one method i found was to create a temp table for Table B which will not have the delete candidates and then rename the temp table to original. This worked. But this will have the complexity of maintaining the indexes and constraints on the renamed tables.
second method i tried was to remove the foreign key and it resulted in deleting the table B data in seconds. But this is not what we want to do in production table.
Is there ant other method which can be used do delete the data without disabling the constraints and what could be the possible reason for the delete to be a long running query ?
Any help is appreciated.
below is the ddl which can be used to create the test data
create table TABLE_A (E_ID number(8));
create table TABLE_B (E_ID number(8) , constraint TABLEB_PK primary key (E_ID));
create table TABLE_C (A_ID number(8),E_ID number(8), constraint TABLEC_PK primary key (A_ID), CONSTRAINT TABLEC_FK foreign key (E_ID) REFERENCES TABLE_B (E_ID));
create table TABLE_D (A_ID number(8),E_ID number(8), constraint TABLED_PK primary key (A_ID), constraint TABLED_FK foreign key (E_ID) references TABLE_B (E_ID));
create table TABLE_E (A_ID number(8),E_ID number(8), constraint TABLEE_PK primary key (A_ID), CONSTRAINT TABLEE_FK foreign key (E_ID) REFERENCES TABLE_B (E_ID));
create index TABLEC_IDX ON TABLE_C (E_ID);
create index TABLED_IDX on TABLE_D (E_ID);
create index TABLEE_IDX ON TABLE_E (E_ID);
CREATE SEQUENCE parent_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
CREATE SEQUENCE child_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
increment by 1
CACHE 20;
begin
for i in 1..1000000 loop
insert into TABLE_B values (PARENT_SEQ.nextval);
if i < 333333 then
insert into TABLE_C values (CHILD_SEQ.nextval,PARENT_SEQ.currval);
end if;
end loop;
commit;
for i in 1..1000000 loop
insert into TABLE_B values (PARENT_SEQ.nextval);
if i < 333333 then
insert into TABLE_D values (CHILD_SEQ.nextval,PARENT_SEQ.currval);
end if;
end loop;
commit;
for i in 1..1000000 loop
insert into TABLE_B values (PARENT_SEQ.nextval);
if i < 333333 then
insert into TABLE_E values (CHILD_SEQ.nextval,PARENT_SEQ.currval);
end if;
end loop;
commit;
end;
/
Message was edited by: rahcha