删除主表时，都会去查找对应的子表，如果子表的外键上没索引当然慢了，可以用sql_trace跟踪一下。
ttx@ttx>create table pk_tables(id number not null,name varchar2(30));
Table created.
Elapsed: 00:00:00.49
ttx@ttx>alter table pk_tables add constraint pk_pk_tables primary key(id) using index;
Table altered.
Elapsed: 00:00:00.37
ttx@ttx>create table fk_tables(ref_id number not null, what varchar2(100));
Table created.
Elapsed: 00:00:00.02
ttx@ttx>alter table FK_TABLES
2add constraint fk_tables foreign key (REF_ID)
3references pk_tables (ID);
Table altered.
Elapsed: 00:00:00.08
ttx@ttx>alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.12
ttx@ttx>insert into pk_tables values(1,'test');
1 row created.
Elapsed: 00:00:00.34
ttx@ttx>insert into pk_tables values(2,'test');
1 row created.
Elapsed: 00:00:00.01
ttx@ttx>commit;
Commit complete.
Elapsed: 00:00:00.01
ttx@ttx>delete pk_tables where id=1;
1 row deleted.
Elapsed: 00:00:00.04
ttx@ttx>commit;
Commit complete.
Elapsed: 00:00:00.00
ttx@ttx>spool off
......
PARSING IN CURSOR #10 len=27 dep=0 uid=55 oct=7 lid=55 tim=33867442042 hv=3897730382 ad='6993201c'
delete pk_tables where id=1
END OF STMT
PARSE #10:c=0,e=1717,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=33867442034
=====================
PARSING IN CURSOR #6 len=75 dep=1 uid=0 oct=3 lid=0 tim=33867467599 hv=419685574 ad='69931c5c'
select /*+ all_rows */ count(1) from "TTX"."FK_TABLES" where "REF_ID" = :1
END OF STMT
PARSE #6:c=0,e=708,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=33867467592
.....
|
