表外键未加索引之处理

就像在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  /

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/628922/viewspace-733184/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/628922/viewspace-733184/