oracle外键有什么用,深入理解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 DISABLE TABLE LOCK;

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

ERROR at line 1：

ORA-00069： cannot acquire lock -- table locks disable for

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

建立表：

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