找出表中未加索引的外键 de 脚本

以前做的Oracle资料，整理的创建索引的几条原则：http://noco.blog.hexun.com/3240983_d.html

1. 需要平衡query合DML的需要，常用于(子)查询的表应建立索引；
2. 把索引建到不同的表空间中；
3. 用统一的extent大小: 五个block的倍数或者tablespace指定的MINIMUM EXTENT的倍数；
4. 创建索引考虑用NOLOGGING参数，重建索引的时候也一样；
5. 创建索引时INITRANS值应该比相应的table的值高一些；
6. 对常用SQL语句的where条件中的列建立唯一索引或组合索引，组合条件查询中相应的组合索引更有效；
7. 对于组合索引，根据列的唯一值概率，安排索引顺序；
8. 如果一个列具有很低的数据基数，并且或者可具有空值，不应作为索引列；
9. 如果where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应函数索引；
10. 对于低基数集的列，并包含OR等逻辑运算，考虑用Bitmap索引，对于从大量行的表中返回大量的行时也可以考虑Bitmap索引；
11. 避免在有大量并发DML运算的表中使用Bitmap索引；

 

 

/*这个脚本将处理外键约束，其中最多可以有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“过分地锁定了”数据。
*/
--查看是不是存在未加索引的外键，而且在99%的情况下都会发现表中确实存在这个问题。只需对外键加索引，
--死锁（以及大量其他的竞争问题）都会烟消云散。下面的例子展示了如何使用这个脚本来找出表C中未加索引的外键

 

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
 )

 /

 

 

 

 

 

 

生成创建index脚本的语句：

select 'create index IX_'||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)||' 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
 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
 )

/