ORACLE外鍵和鎖

標籤：style   blog   color   os   io   資料   div   問題   sp   

在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外鍵和鎖