Sunday, April 10, 2011

Impact on Foreign key constraint and Index on FK column on DML occurred in parent table

create table tbl_p (pid number, col1 char(80));
create table tbl_c (cid number, clo1 char(80),pid number);
create unique index p_pk on tbl_p(pid);
create unique index c_pk on tbl_c(cid);
create index c_n1 on tbl_c(pid);
alter table tbl_p add constraint p_pk primary key (pid);
alter table tbl_c add constraint c_pk primary key (cid);
alter table tbl_c add constraint c_p_fk foreign key (pid) references tbl_p(pid);

SQL> insert into tbl_p select rownum,'x' from dual connect by rownum<=10000;
10000 rows created.
SQL> insert into tbl_c select rownum,'y',rownum from dual connect by rownum<=10000;
10000 rows created.
SQL> commit;


SQL> select segment_name,sum(blocks) from dba_extents where owner='DONGHUA' group by segment_name;

SEGMENT_NAME SUM(BLOCKS)
------------------------------ -----------
P_PK 24
TBL_P 256
C_PK 24
TBL_C 256
C_N1 32

SQL> set autotrace traceonly statistics;


-- delete from tbl_c where pid=(select pid from tbl_p where rowid='AAASfKAAEAAAAIPAAA');

SQL> delete from tbl_p where rowid='AAASfKAAEAAAAIPAAA';

1 row deleted.


Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1 consistent gets
0 physical reads
116 redo size
672 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> drop index c_n1;

Index dropped.

-- delete from tbl_c where pid=(select pid from tbl_p where rowid='AAASfKAAEAAAAIPAAB');

SQL> delete from tbl_p where rowid='AAASfKAAEAAAAIPAAB';

1 row deleted.


Statistics
----------------------------------------------------------
14 recursive calls
7 db block gets
232 consistent gets
0 physical reads
816 redo size
674 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

-- same effect as alter table tbl_c drop constraint c_p_fk;
SQL> alter table tbl_c disable constraint c_p_fk;


Table altered.

-- delete from tbl_c where pid=(select pid from tbl_p where rowid='AAASfKAAEAAAAIPAAC');

SQL> delete from tbl_p where rowid='AAASfKAAEAAAAIPAAC';

1 row deleted.


Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
1 consistent gets
0 physical reads
116 redo size
676 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed