Oracle cascade delete

Question

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

Wardwarde

Answer 1

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.

Operational

so where is the answer ? what is more efficient. – Velarium

@magulla: As with most things in Oracle, it depends. It may be more efficient to manually delete many child rows before doing the delete, but nothing prevents another session from inserting more children while that's going on. Considering that Oracle issues a delete for every parent row deleted if children exist, if performance is paramount, do your best to delete the child table's rows of interest with a single statement would be a rule of thumb, but "better" performance is not guaranteed. – Operational

Answer 2

You can not compare both options like this. its not a performance issue but more design and structure.

If you design your database using primary / foreign keys it will be easier to delete using cascading-deletes than searching manually where do you have foreign keys on which column and table and generate matching SQL statements.

The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions

Goebbels

Answer 3

Cascade delete is better to avoid deadlock issues while deleting the child entries. I resolved one performance issue in my application related to individual deleting of child entries. We replaced with cascade and now everything is working fine.

Geryon

Answer 4

If you want to cascade delete and don't have a foreign key defined, you can use something like this:

DELETE FROM my_table
 WHERE ROWID IN
     ( SELECT ROWID
         FROM my_table
        START WITH (condition_on_the_row_that_you_want_to_delete)
      CONNECT BY PRIOR (primary_key) = (self_foreign_key)
     )

Rothberg

The downside to such a solution would be that any rows inserted that your session running the delete doesn't see will still be inserted - and orphaned, as you've killed the parent, but the child rows still get inserted. – Operational

@AdamMusch Do you have a solution for you mentioned scenario? – Walston