外键未加索引
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