深入理解Oracle索引(20)：外键是否应该加索引

先表明我的立场、我是绝对支持外键一定要加索引！
虽然在高版本的Oracle里、对这个要求有所降低、但依然有如下原因：

① 死锁

外键未加索引是导致死锁的最主要原因、因为
无论更新父表主键、或者删除一个父表记录、都会在子表加一个表锁
这就会不必要的锁定更多的行、从而影响并发性


② ON DELETE CASCADE

对于删除的每一个父行、都会把子表全表扫描一次
如：
EMP是DEPT的子表
DELETE dept WHERE deptno=10 会级联至EMP


③ 从父表查询子表

如：
EMP是DEPT的子表
SELECT *
FROM dept,emp
WHERE emp.deptno=dept.deptno and
dept.dname= :X


另外、证明子表由于外键未加索引而被锁住、可经由下列方法：

ALTER TABLE <child table name> DISABLE TABLE LOCK;

那么、对父表的可能导致表锁的任何 UPDATE 或 DELETE 都会收到如下错误：

ERROR at line 1：

ORA-00069： cannot acquire lock -- table locks disable for <child table name>

以下做个简单的外键未加索引的测试：

建立表：

hr@ORCL> create table t_father (id number,name varchar2(25),primary key(id));
hr@ORCL> create table t_sun (fid number,name varchar2(25),foreign key(fid) references t_father(id));

hr@ORCL> select table_name,CONSTRAINT_NAME,STATUS,R_CONSTRAINT_NAME from user_constraints where owner='HR' and table_name in ('T_FATHER','T_SUN');

TABLE_NAME                     CONSTRAINT_NAME                STATUS   R_CONSTRAINT_NAME
------------------------------ ------------------------------ -------- ------------------------------
T_FATHER                       SYS_C005495                    ENABLED
T_SUN                          SYS_C005497                    ENABLED  SYS_C005495

倒入数据并分析表：

hr@ORCL> insert into t_father select rownum,rownum||'a' from dual connect by rownum<1000;
hr@ORCL> insert into t_sun select rownum,rownum||'b' from dual connect by rownum<1000;
hr@ORCL> commit;
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T_FATHER');
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T_SUN');



用以下 TOM 给出的脚本检查外键无索引的表：

COLUMN COLUMNS format a30 word_wrapped
COLUMN tablename format a15 word_wrapped
COLUMN constraint_name format a15 word_wrapped
SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
       NVL2(CNAME3, ',' || CNAME3, NULL) ||
       NVL2(CNAME4, ',' || CNAME4, NULL) ||
       NVL2(CNAME5, ',' || CNAME5, NULL) ||
       NVL2(CNAME6, ',' || CNAME6, NULL) ||
       NVL2(CNAME7, ',' || CNAME7, NULL) ||
       NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
  FROM (SELECT B.TABLE_NAME,
               B.CONSTRAINT_NAME,
               MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
               MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
               MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
               MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
               MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
               MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
               MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
               MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
               COUNT(*) COL_CNT
          FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
                       SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                       SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
                       POSITION
                  FROM USER_CONS_COLUMNS) A,
               USER_CONSTRAINTS B
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND B.CONSTRAINT_TYPE = 'R'
         GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
 WHERE COL_CNT > ALL
 (SELECT COUNT(*)
          FROM USER_IND_COLUMNS I
         WHERE I.TABLE_NAME = CONS.TABLE_NAME
           AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
                CNAME6, CNAME7, CNAME8)
           AND I.COLUMN_POSITION <= CONS.COL_CNT
         GROUP BY I.INDEX_NAME)
/



hr@ORCL> /

TABLE_NAME                     CONSTRAINT_NAME COLUMNS
------------------------------ --------------- ------------------------------
T_SUN                          SYS_C005497     FID



以下进行测试：

1）Session_A:

hr@ORCL> select sid from v$session where sid in (select sid from v$mystat where rownum=1);

       SID
----------
       159


hr@ORCL> delete t_sun where fid=998;

1 row deleted.




2）Session_B:

hr@ORCL> select sid from v$session where sid in (select sid from v$mystat where rownum=1);

       SID
----------
       142
       
hr@ORCL> delete t_sun where fid=123;

1 row deleted.


3）Session_A：

hr@ORCL> delete t_father where id=555;

----请求子表的表锁却不可得之、被hang住了



用下面脚本查询数据库锁情况：

SELECT a.sid ||
         decode(request,
                0,
                ' :holder',' :Waiter') sess_id,blocking_session blocker,
         lmode,
         request,
         a.type,
         c.object_name,
         decode(row_wait_obj#,
                -1,
                'Holder of Lock !!!',
                dbms_rowid.rowid_create(1,
                                        row_wait_obj#,
                                        row_wait_file#,
                                        row_wait_block#,
                                        row_wait_row#)) row_id,
         nvl(SQL_FULLTEXT, 'Holder of Lock !!!') sqltext
 FROM V$LOCK A, V$LOCKED_OBJECT B, ALL_OBJECTS C, V$SESSION D, V$SQL E
WHERE (id1, id2, a.type) in
         (select id1, id2, type from v$lock where request > 0)
     AND a.sid = b.session_id
     AND b.object_id = c.object_id
     AND d.sid = a.sid
     AND d.sql_hash_value = e.hash_value(+)
     
     

sys@ORCL> /

SESS_ID                                             BLOCKER      LMODE    REQUEST TY OBJECT_NAME                    ROW_ID           SQLTEXT
------------------------------------------------ ---------- ---------- ---------- -- ------------------------------ ------------------ --------------------------------------------------------------------------------
159 :Waiter                                             142          3          5 TM T_SUN                          Holder of Lock !!! delete t_father where id=555
159 :Waiter                                             142          3          5 TM T_FATHER                       Holder of Lock !!! delete t_father where id=555
142 :holder                                                          3          0 TM T_SUN                          Holder of Lock !!! Holder of Lock !!!
142 :holder                                                          3          0 TM T_FATHER                       Holder of Lock !!! Holder of Lock !!!

     

By David Lin
2013-06-07
Good Luck