..
drop table c1;
drop table c2;
drop table p1;
drop table p2;
set echo on
set timing on

/*
set up parent child WITHOUT constrained type...
*/
create table p1 ( x number(12) primary key);
create table c1 ( x number references p1 on delete cascade);
create index c1_idx on c1(x);

/*
set up parent child WITH constrained type...
*/
create table p2 ( x number(12) primary key);
create table c2 ( x number(12) references p2 on delete cascade );
create index c2_idx on c2(x);

/*
created some parent data...
*/
insert into p1 select rownum from all_objects;
insert into p2 select rownum from all_objects;
commit;

alter session set events '10046 trace name context forever, level 12';

/*
test BULK inserts into child
*/
insert into c1 select * from p1;
insert into c2 select * from p2;

/*
and then single row inserts...
*/
begin
for x in ( select * from p1 )
loop
for i in 1 .. 5
loop
insert into c1 values ( x.x );
end loop;
commit;
end loop;
end;
/
begin
for x in ( select * from p2 )
loop
for i in 1 .. 5
loop
insert into c2 values ( x.x );
end loop;
commit;
end loop;
end;
/

update c1 set x = x+1 where x < 1000;
commit;
update c2 set x = x+1 where x < 1000;
commit;

begin
for x in ( select * from c1 where x between 5000 and 5500)
loop
update c1 set x = x+1 where x = x.x;
commit;
end loop;
end;
/
begin
for x in ( select * from c2 where x between 5000 and 5500)
loop
update c2 set x = x+1 where x = x.x;
commit;
end loop;
end;
/

/*
now some BULK deletes with the on delete cascade
*/
delete from p1 where mod(x,2) = 0;
commit;
delete from p2 where mod(x,2) = 0;
commit;


/*
and then single row deletes
*/
begin
for x in ( select * from p1 )
loop
delete from p1 where x = x.x;
end loop;
end;
/
commit;

begin
for x in ( select * from p2 )
loop
delete from p2 where x = x.x;
end loop;
end;
/
commit;