表外來鍵未加索引之處理

就像在Tom程式設計藝術中提到的，如果資料庫中確實存在死鎖，那麼需要檢視是否存在未加索引的外來鍵，而且在99%的情況下都會發現表中確實存在這個問題。只需對外來鍵加索引，死鎖（以及大量其他的競爭問題）都會煙消雲散。下面的例子就展示瞭如何使用這個指令碼來找出未加索引的外來鍵：
SQL> show user
USER is "SCOTT"
SQL> col columns for a20

SQL>
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,ame4,
 cname5, cname6, cname7, cname8 )
 and i.column_position <= cons.col_cnt group by i.index_name
 26   )
SQL> /

TABLE_NAME               CONSTRAINT_NAME              COLUMNS
------------------------------ ------------------------------ --------------------
C                   SYS_C006026              X
EMP                   FK_DEPTNO              DEPTNO
SCHEME_LABLE_RERATION_TABLE    FK_SCHEME_L_REFERENCE_LABLEINF LABLE_ID

這個指令碼將處理外來鍵約束，其中最多可以有8列（如果你的外來鍵有更多的列，可能就得重新考慮一下你的設計了）。
首先，它在前面的查詢中建立一個名為CONS的內聯檢視（inline view）。這個內聯檢視將約束中適當的列名從行轉置到列，其結果是每個約束有一行，最多有8 列，這些列分別取值為約束中的列名。另外，這個檢視中還有一個列COL_CNT，
其中包含外來鍵約束本身的列數。對於這個內聯檢視中返回的每一行，我們要執行一個關聯子查詢（correlated subquery），檢查當前所處理表上的所有索引。它會統計出索引中與外來鍵約束中的列相匹配的列數，然後按索引名分組。這樣，就能生成一組數，每個數都是該表某個索引中匹配列的總計。如果原來的COL_CNT大於所有這些數，那麼表中就沒有支援這個約束的索引。如果COL_CNT小於所有這些數，就至少有一個索引支援這個約束。注意，這裡使用了NVL2 函式，我們用這個函式把列名列表“粘到”一個用逗號分隔的列表中。這個函式有3 個引數：A、B 和C。如果引數A非空，則返回B；否則返回引數C。這個查詢有一個前提，假設約束的所有者也是表和索引的所有者。如果另一位使用者對錶加索引，或者表在另一個模式中（這兩種情況都很少見），就不能正確地工作。所以，這個指令碼展示出，表C在列X上有一個外來鍵，但是沒有索引。透過對X加索引，就可以完全消除這個鎖定問題。
除了全表鎖外，在以下情況下，未加索引的外來鍵也可能帶來問題：
--如果有ON DELETE CASCADE，而且沒有對子表加索引：例如，EMP是DEPT的子表，DELETE DEPTNO = 10應該CASCADE（級聯）至EMP[4]。如果EMP中的DEPTNO沒有索引，那麼刪除DEPT 表中的每一行時都會對EMP 做一個全表掃描。這個全表掃描可能是不必要的，而且如果從父表刪除多行，父表中每刪除一行就要掃描一次子表。
--從父表查詢子表：再次考慮EMP/DEPT 例子。利用DEPTNO 查詢EMP 表是相當常見的。如果頻繁地執行以下查詢（例如，生成一個報告），你會發現沒有索引會使查詢速度變慢：
§ select * from dept, emp
§ where emp.deptno = dept.deptno and dept.deptno = :X;
那麼，什麼時候不需要對外來鍵加索引呢？
答案是，一般來說，當滿足以下條件時不需要加索引：
--沒有從父表刪除行。
--沒有更新父表的惟一鍵/主鍵值（當心工具有時會無意地更新主鍵！）。
--沒有從父表聯結子表（如DEPT聯結到EMP）。
如果滿足上述全部3 個條件，那你完全可以跳過索引，不需要對外來鍵加索引。如果滿足以上的某個條件，就要當心加索引的後果。這是一種少有的情況，即Oracle“過分地鎖定了”資料。

然後，可用一下sql生成建立index的語句：
SQL>
select 'create index IX_'||table_name||'_'|| cname1 || nvl2(cname2,'_'||cname2,null) || nvl2(cname3,'_'||cname3,null) || nvl2(cname4,'_'||cname4,null) ||l) ||
 nvl2(cname5,'_'||cname5,null) || nvl2(cname6,'_'||cname6,null) || nvl2(cname7,'_'||cname7,null) || nvl2(cname8,'_'||cname8,null)||' ON '||table_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 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,ame4,
 cname5, cname6, cname7, cname8 ) and i.column_position <= cons.col_cnt group by i.index_name
 16   )
 17  /