Fw foriegn key and indices

Oracle中更新主外键时的一些注意事项
发表人:winkexp | 发表时间: 2005年一月18日, 23:58
这是很早前看tom的经典文章expert 101时的练习笔记，也许当时的理解并不深刻....现在不做开发了好多东西已经模糊了很多。


更新主外键时的一些注意事项
一、更新主外键

用scott用户打开两个窗口


1、外键无索引时，子表更新外键未提交，主表更新非子表引用的主键时被阻塞


会话1：


create table t1 (x int primary key);

insert into t1 values(1);

insert into t1 values(2);

insert into t1 values(3);

commit;

create table t2（y int references t1）；

insert into t2 values（1）；

commit;

update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=3; //会话被阻塞


2、外键有索引时，子表更新外键未提交，主表更新非子表引用的主键时不会被阻塞


会话1：


create index t2_index on t2(y) ; //创建外键索引

update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=3;

已更新 1 行；//可以正常更新


3、外键有无索引，对于子表更新外键未提交，主表更新相对应的主键无影响，更新主键的session都会被阻塞


会话1：


update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=1; //更新子表已引用的


会话被阻塞。


会话1：


update t2 set y=2 where y=1;


会话2：


update t1 set x=4 where x=2 ; //更新子表将要引用的

会话被阻塞。――很好理解，主表要判断是否违反约束


二、更新子表非外键列未提交


1、外键无索引，更新主表已被外键引用的主键时，更新主键的session被阻塞


会话1：


create table t1 (x int primary key,x1 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

commit ;


create table t2(y int references t1,y1 int);

insert into t2 values(1,1);

commit ;

update t2 set y1=2 where y1=1;


会话2：



update t1 set x=4 where x=1; //更新外键引用的主键

会话被阻塞。


2、外键有索引，更新主表已被外键引用的主键时，更新主键的session不会被阻塞而报约束错误


会话1：


create index t2_index on t2(y);

update t2 set y1=2 where y1=1;


会话2：


update t1 set x=4 where x=1

*

ERROR 位于第 1 行:


ORA-02292: 违反完整约束条件 (SCOTT.SYS_C001607) - 已找到子记录日志


3、外键无索引，更新主表未被外键引用的主键时，更新主键的session被阻塞


会话1：


drop index t2_index；

update t2 set y1=2 where y1=1;


会话2:


update t1 set x=4 where x=2;

会话被阻塞。


4、外键有索引，更新主表未被外键引用的主键时，更新主键的session不会被阻塞


会话1：


create index t2_index on t2(y);

update t2 set y1=2 where y1=1;


会话2：


update t1 set x=4 where x=2;
已更新 1 行。



另外在一个主表有on delete cascade，子表没有外键索引时，对主表操作会级联到子表，子表将进行全表扫描。

总结：在需要更新主键的情况下，最好是创建子表的外键索引。

list foriegn key constraints without indeces

COLUMN COLUMNS format a30 word_wrapped
COLUMN table_name format a30 word_wrapped
COLUMN constraint_name format a30 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 
       /* convert relation constraints columns to single row format */
       (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 
               /* format name into 30 chars */
               (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 
 /* make sure the constraints can not utilize the indices */
 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)  
/