ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 找出表中未加索引的外键 de 脚本

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

原创 Linux操作系统 作者:jichenghan 时间:2009-08-20 17:08:33 0 删除 编辑

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

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

/

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/djs00717/archive/2009/04/25/4105791.aspx

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

下一篇: 补丁下载
请登录后发表评论 登录
全部评论

注册时间:2008-02-26

  • 博文量
    17
  • 访问量
    36344