和外键相关的一点性能问题总结！

最新推荐文章于 2022-08-11 14:02:28 发布

clt3617

最新推荐文章于 2022-08-11 14:02:28 发布

阅读量160

点赞数

文章标签：数据库

当存在主外键关系的时候，如果外键上没有索引，而且从表(有外键的表)又比较大时，一些操作主表的动作，如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

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,很显然效果非常好.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1039622/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1039622/