Is cascade delete on a table more efficient than individual delete statements (executed in a single plsql block) ?
Advertisement
Answer
What cascade delete does is issue individual delete statements.
Examine the following test case:
create table parent
(parent_id number,
parent_name varchar2(30),
constraint parent_pk primary key (parent_id) using index);
create table child
(child_id number,
parent_id number,
child_name varchar2(30),
constraint child_pk primary key (parent_id, child_id) using index,
constraint child_fk01 foreign key (parent_id)
references parent (parent_id)
on delete cascade;
);
insert into parent
(parent_id, parent_name)
select object_id, object_name from dba_objects where rownum <= 10000;
begin
for i in 1..10
loop
insert into child
(child_id, parent_id, child_name)
select i, parent_id, parent_name
from parent;
end loop;
end;
/
exec dbms_stats.gather_table_stats (tabname => 'PARENT', cascade => true);
exec dbms_stats.gather_table_stats (tabname => 'CHILD', cascade => true);
exec dbms_monitor.session_trace_enable;
alter table child drop constraint child_fk01;
alter table child add constraint child_fk01 foreign key (parent_id)
references parent (parent_id) on delete cascade enable novalidate ;
delete from parent;
rollback;
In the trace file, you will find a line like this:
delete from "<MY_SCHEMA_NAME>"."CHILD" where "PARENT_ID" = :1 END OF STMT PARSE #6:c=0,e=182,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1293353992514766 EXEC #6:c=0,e=545,p=0,cr=2,cu=32,mis=1,r=10,dep=1,og=4,tim=1293353992515354 EXEC #6:c=0,e=233,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992515644 EXEC #6:c=0,e=238,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992515931 EXEC #6:c=0,e=252,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992516229 EXEC #6:c=0,e=231,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992516507 EXEC #6:c=0,e=227,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992516782 EXEC #6:c=0,e=244,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992517072 EXEC #6:c=0,e=219,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517337 EXEC #6:c=0,e=236,p=0,cr=3,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517622 EXEC #6:c=0,e=235,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992517921 EXEC #6:c=0,e=229,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992518196 EXEC #6:c=0,e=246,p=0,cr=2,cu=32,mis=0,r=10,dep=1,og=4,tim=1293353992518487 EXEC #6:c=0,e=234,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992518767 EXEC #6:c=6999,e=570,p=0,cr=2,cu=30,mis=0,r=10,dep=1,og=4,tim=1293353992519383
That is Oracle issuing a delete statement against CHILD for each record it’s deleting in PARENT.
A different question would be which of the two are more efficient:
DELETE FROM CHILD WHERE PARENT_ID = 1; DELETE FROM PARENT WHERE PARENT_ID = 1;
vs
DELETE FROM PARENT WHERE PARENT_ID = 1;
both with on delete cascade enabled. Suprisingly enough, in the first case above, Oracle will probe the foreign key index on the child table to see if any rows exist which would require a cascade. If no rows exist, Oracle does not execute the cascaded delete.