Truncate Table Cascade

Eine nützliche Ergänzung in 12c, die mir bisher entgangen war, ist das TRUNCATE CASCADE, mit dessen Hilfe man eine Parent-Tabelle entleeren kann und zugleich alle Datensätze in via FK verbundenen Tabellen löscht. Das funktionierte bislang nur mit DELETE - und auf DELETE verzichte ich gerne, wo immer das möglich ist. Dazu ein kleines Beispiel:

drop table child;
drop table parent;

create table parent(
    parent_id number primary key
);

insert into parent(parent_id)
select rownum 
  from dual
connect by level <= 10000;  

create table child(
    id number primary key
  , parent_id number
  , constraint fk_parent_id foreign key(parent_id) references parent(parent_id) on delete cascade
);

insert into child(id, parent_id)
select rownum 
     , rownum
  from dual
connect by level <= 10000;  

insert into child(id, parent_id)
values(-1, null);

create index child_fk_idx on child(parent_id);

Ausgehend von diesem Versuchsaufbau lassen sich die Unterschiede in Performance und Verhalten leicht überprüfen:

delete from parent cascade;

--> 10000 Zeilen gelöscht.
--> Abgelaufen: 00:00:01.54

select * from child where id < 0;

        ID  PARENT_ID
---------- ----------
        -1

rollback;

truncate table parent cascade;

--> Tabelle mit TRUNCATE geleert
--> Abgelaufen: 00:00:00.07

select * from child where id < 0;

Es wurden keine Zeilen ausgewählt

Ursprünglich wollte ich hier noch diverse v$sesstat-Angaben unterbringen, aber das unterschiedliche Verhalten hinsichtlich des verwaisten Satzes mit id -1 in der child-Tabelle macht ziemlich deutlich, was hier passiert:

• das DELETE CASCADE löscht alle Sätze aus parent und alle via FK verknüpften Sätze aus child. Das dauert für 10000 Datensätze auf meiner Workstation bereits 1,5 sec, lässt aber den Orphan-Datensatz aus child (id = -1) stehen.
• das TRUNCATE CASCADE leert parent und child komplett, was deutlich schneller durchgeführt werden kann als das DELETE, aber den Orphan-Satz nicht stehen lässt. Es handelt sich also um ein TRUNCATE auf beiden Ebenen - nicht um ein TRUNCATE für parent und ein DELETE aller Sätze ohne FK-Beziehung in der abhängigen Tabelle.

Wenn man die Dokumentation liest, ehe man das Verhalten testet, ist das Ergebnis nicht besonders erstaunlich:

If you specify CASCADE, then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.

Ich kann mir durchaus Fälle vorstellen, in den dieses Verhalten genau das ist, was man haben möchte - aber auch solche, in denen es nicht den Erwartungen der Anwender entspricht. Auf jeden Fall sollte man sich einprägen, dass DELETE CASCADE und TRUNCATE CASCADE unterschiedliche Ergebnisse hervorrufen.