通過在Oracle子表外來鍵上建立索引提高效能

物理狂人發表於2012-02-20
Oracle索引
根據我的經驗，導致死鎖的頭號原因是外來鍵未加索引（第二號原因是表上的點陣圖索引遭到併發更新）。在以下兩種情況下，Oracle在修改父表後會對子表加一個全表鎖：
?1）如果更新了父表的主鍵（倘若遵循關聯式資料庫的原則，即主鍵應當是不可變的，這種情況就很少見），由於外來鍵上沒有索引，所以子表會被鎖住。
?2） 如果刪除了父表中的一行，整個子表也會被鎖住（由於外來鍵上沒有索引）。

除了全表鎖外，在以下情況下，未加索引的外來鍵也可能帶來問題：
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;

那麼，什麼時候不需要對外來鍵加索引呢？答案是，一般來說，當滿足以下條件時不需要加索引：
1）? 沒有從父表刪除行。
2）? 沒有更新父表的惟一鍵/主鍵值（當心工具有時會無意地更新主鍵！）。
3）? 沒有從父表聯結子表（如DEPT聯結到EMP）。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24104518/viewspace-716784/，如需轉載，請註明出處，否則將追究法律責任。

相關文章