Oracle cascade delete

Is cascade delete on a table more efficient than individual delete statements (executed in a single plsql block) ?

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.