外键未加索引

Question

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 deletedSQL> 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         NoneSQL> rollback;Rollback completeSQL> 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 deletedSQL> 
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，仍就导致子表被锁定？？？？

Answer 1

oracle和sql server的区别
oracle的主外键没有什么用处的，只会降低性能【包括查询】，只起到约束作用，而且还可以指定约束不起作用的。
所以，现在的开发，oracle都不设置主外键了。

Answer 2

学习学习，原来oracle外键可以不用设呀，原来用sqlserver，呵呵。

Answer 3

不用Foreign key !!!!!
这个我不太认同。

Answer 4

2、子表FK增加索引后，对父表的DELETE，仍就导致子表被锁定？？？？   创表的时候加了级联删除，删除主表数据的时候，子表的数据肯定是要锁的。这与加不加外键索引没有关系的，因为删除主表数据之前，oracle会先删除子表的数据。
  级联删除，就类似于你在主表上创建了before delete触发器一样，只不过这些代码数据库替你写了。
  另外，外键约束是在数据库的层面上，保证数据的完整性，如果系统对数据的要求非常高，最好在表设计时增加外键约束。

Answer 5

创表的时候加了级联删除，删除主表数据的时候，子表的数据肯定是要锁的。这与加不加外键索引没有关系的，因为删除主表数据之前，oracle会先删除子表的数据。
有关系！！！如果你在外键上加了索引那么：
1.在级联删除的时候oracle只会锁子表中的相关数据，如果在外键上不加索引的话oracle会锁住整个子表。
2.在关联主表和子表进行查询时，如果加了索引那么oracle就不会对子表进行全表扫描了。

Answer 6

我来看看你的问题1  如果该主键值在子表中有引用，则UPDATE语句违反了外键完整性约束。
这个没用错，这个父键是不能被更新的2如果该主键值在子表中没引用，则UPDATE语句也不会将子表锁定。
楼主 你把一切太想当然了，ORACLE怎么知道该主键值在子表中没用被引用？ 所以ORACLE要从子表中找，看到底要修改的主键值是否在子表中有记录，加上子表中对外键没有索引，所以ORACLE不得不执行全表扫描，并在全表扫描期间不允许任何会话修改子表中的记录。这样就锁定了全子表。2子表增加索引后，对父表的DELETE，仍就导致子表被锁定？？？
楼主没搞懂加了索引和没加索引的区别，没加索引，父表从子表中查数据的时候要锁定全表，加了索引，父表从子表中查询数据只要锁定从父表中删除的那几行而已。虽然楼主从前后2个测试来看，表锁都是一样的，但是没加索引的时候是锁定了全子表，加了索引只锁定了子表中的那一行。