外键未加索引
Dear All:
最近学习TOM大作时,遇到一点疑惑。描述如下:
父表Dept
(1、DEPTNO字段为PK)
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
子表Emp
(1、DEPTNO字段为FK,且设置了级联删除
2、DEPTNO当前不存在索引)
create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
alter table EMP
add constraint FK_DEPTNO foreign key (DEPTNO)
references DEPT (DEPTNO) on delete cascade;
下面针对子表外键没有和增加索引的情况,进行测试。
情况1:子表外键没有索引
会话1(测试父表DEPT的DML操作):
SQL> delete from dept where deptno = 10;
1 row deleted
SQL> select sid, type,b.object_name,
2 decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
3 decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock
4 from v$lock, user_objects b
5 where b.object_id(+) = id1
6 and sid = (select sid from v$mystat where rownum =1);
SID TYPE OBJECT_NAME HOLD_LOCK REQUEST_LOCK
---------- ---- -------------------------------------------------------------------------------- --------- ------------
154 TM DEPT RX None
154 TM EMP RX None
154 TX X None
SQL> rollback;
Rollback complete
SQL> update dept set dname = upper(dname) where deptno = 10;
1 row updated
SQL> select sid, type,b.object_name,
2 decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
3 decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock
4 from v$lock, user_objects b
5 where b.object_id(+) = id1
6 and sid = (select sid from v$mystat where rownum =1);
SID TYPE OBJECT_NAME HOLD_LOCK REQUEST_LOCK
---------- ---- -------------------------------------------------------------------------------- --------- ------------
154 TM DEPT RX None
154 TX
结论:
1、对父表DEPT进行DELETE操作,子表EMP增加了RX级别的TM锁。
2、对父表DEPT的进行UPDATE操作,子表EMP未增加锁。
情况2:子表EMP外键增加索引
(注:测试过程中,FK的索引 已使用过 NORMAL 和 BITMAP 两种类型,以下用NORMAL索引为例)
create index FK_EMP_IDEX on EMP (DEPTNO) tablespace USERS..
会话1:
SQL> delete from dept where deptno = 10;
1 row deleted
SQL>
SQL> select sid, type,b.object_name,
2 decode(lmode,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') hold_lock,
3 decode(request,0,'None',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request_lock
4 from v$lock, user_objects b
5 where b.object_id(+) = id1
6 and sid = (select sid from v$mystat where rownum =1);
SID TYPE OBJECT_NAME HOLD_LOCK REQUEST_LOCK
---------- ---- -------------------------------------------------------------------------------- --------- ------------
133 TM DEPT RX None
133 TM EMP RX None
133 TX
结论:
1、对FK增加索引后,父表DEPT的DELETE操作,子表EMP依然被锁。
问题:
按测试结果来看,
1、子表FK未加索引时,对父表的UPDATE时,子表不会被锁定。
假设父表更新的是主键 update dept set deptno = deptno + 2 where dept = 10;
如果该主键值在子表中有引用,则UPDATE语句违反了外键完整性约束。
如果该主键值在子表中没引用,则UPDATE语句也不会将子表锁定。
这与TOM所说“如果更新父表的主键,由于外键没有索引,子表会被锁住” 就有矛盾了????
2、子表FK增加索引后,对父表的DELETE,仍就导致子表被锁定????