ORACLE外键和锁

在oracle中，如果外键未加索引，对父表的修改，会导致子表被加上全表锁。这包括两种情况：

1.删除父表中的行，如果外键上没有索引，会导致子表被加上全表锁

2.更新父表的主键（根据关系数据库的原则，更新主键是一个巨大的”禁忌”，所以一般不会出现这种情况），如果外键上没有索引，会导致子表被加上全表锁

虽然，在Oracle9i及以上的版本中，这些全表锁都是短期的，他们仅在DML操作期间存在，而不是在整个事务的期间都存在。但是即使如此，这些全表锁还是可能(而且确实会)导致很严重的锁定问题。

可以通过如下语句，查询是否存在未加索引的外键：

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)

这个脚本将最多处理8列外键约束(如果你的外键有更多的列，可能就得重新考虑一下你的设计了)！

除了全表锁外，在以下情况下，未加索引的外键也可能带来问题：

1.如果有on delete cascade,而且没有对子表加索引：例如,emp是dept的子表，delete deptno=10应该cascade(级联)至emp。如果emp中的deptno没有索引，那么删除dept表中的每一行时都会对emp做一次全表扫描。这个全表扫描可能是不必要的，而且如果从父表删除多行，父表中每删除一行就要扫描仪一次子表。

2.从父表查询子表：在此考虑emp/dept例子。利用deptno查询emp表是相当常见的。如果频繁地运行以下查询，你就会发现没有索引会使查询速度变慢：

select *

     from dept, emp

    where emp.deptno=dept.deptno

      and dept.deptno=:x;

所以，要特表注意是否需要对外键加索引，防止出现这种Oracle“过分地锁定了”数据的情况。

--参考自《Oracle专家高级编程》

巴特西

ORACLE外键和锁

最新文章

热门文章