和外來鍵相關的一點效能問題總結！

當存在主外來鍵關係的時候，如果外來鍵上沒有索引，而且從表(有外來鍵的表)又比較大時，一些操作主表的動作，如delete可能會消耗很大的資源，因為在操作主表的同時oracle內部需要訪問從表。[@more@]

SQL> create table t tablespace users as select * from dba_objects;

表已建立。

SQL> alter table t add constraint pk_t primary key (object_id);
alter table t add constraint pk_t primary key (object_id)
*
第 1 行出現錯誤:
ORA-01449: 列包含 NULL 值; 無法將其變更為 NOT NULL

SQL> delete from t where object_id is null ;

已刪除2行。

SQL> commit;

提交完成。

SQL> alter table t add constraint pk_t primary key (object_id);

表已更改。

SQL> create table tt tablespace users as select * from dba_objects;

表已建立。

SQL> insert into tt select * from tt;

已建立11661行。

SQL> insert into tt select * from tt;

已建立23322行。

SQL> insert into tt select * from tt;

已建立46644行。

SQL> commit;

提交完成。

SQL> delete from tt where object_id is null ;

已刪除16行。

SQL> commit;

提交完成。

SQL> alter table tt add constraint fk_tt foreign key (object_id) references t(ob
ject_id);
alter table tt add constraint fk_tt foreign key (object_id) references t(object_
id)
*
第 1 行出現錯誤:
ORA-02298: 無法驗證 (SYS.FK_TT) - 未找到父項關鍵字

SQL> select distinct object_id from tt where object_id not in (select object_id
from t);

OBJECT_ID
----------
12998
12997

SQL> delete from tt where object_id in (12997,12998);

已刪除16行。

SQL> commit;

提交完成。

SQL> alter table tt add constraint fk_tt foreign key (object_id) references t(ob
ject_id) on delete cascade;

表已更改。

SQL> alter session set sql_trace=true;

會話已更改。

SQL> delete from t where object_id=2;

已刪除 1 行。

SQL> alter session set sql_trace=false;

會話已更改。

SQL>
--=====================
C:>tkprof G:oracleproduct10.2.0adminorcludumporcl_ora_2100.trc d.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 10月 12 12:12:29 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

C:>
--=====================
********************************************************************************

delete from t
where
object_id=2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 1 0 0
Execute 1 0.00 0.00 0 2 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.07 0.07 0 3 6 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE T (cr=1195 pr=0 pw=0 time=6786 us)
1 INDEX UNIQUE SCAN PK_T (cr=2 pr=0 pw=0 time=13 us)(object id 12997)

********************************************************************************

delete from "SYS"."TT"
where
"OBJECT_ID" = :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 1193 8 8
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 1193 8 8

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE TT (cr=1193 pr=0 pw=0 time=5585 us)
8 TABLE ACCESS FULL TT (cr=1193 pr=0 pw=0 time=10073 us)

********************************************************************************

alter session set sql_trace=false
--==============================
--沒有index時訪問tt的邏輯讀是1193(上面query對應的值)而且訪問tt
使用的是全表掃面，下面在tt表的外來鍵上建立index看看效果...
SQL> create index idx_tt on tt(object_id) tablespace users;

索引已建立。

SQL> alter session set sql_trace=true;

會話已更改。

SQL> delete from t where object_id=4;

已刪除 1 行。
SQL> commit;

提交完成。

SQL> alter session set sql_trace=false;

會話已更改。

--==============================
delete from t
where
object_id=4

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 2 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 2 6 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE T (cr=4 pr=0 pw=0 time=11188 us)
1 INDEX UNIQUE SCAN PK_T (cr=2 pr=0 pw=0 time=26 us)(object id 12997)

********************************************************************************

delete from "SYS"."TT"
where
"OBJECT_ID" = :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 24 8
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 24 8

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE TT (cr=2 pr=0 pw=0 time=358 us)
8 INDEX RANGE SCAN IDX_TT (cr=2 pr=0 pw=0 time=53 us)(object id 13006)

--=================================
--有index時訪問tt的邏輯讀是2(上面query對應的值)而且訪問tt
使用的是INDEX RANGE SCAN IDX_TT,很顯然效果非常好.