ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表外键未加索引之处理

表外键未加索引之处理

原创 Linux操作系统 作者:lwitpub 时间:2012-06-18 16:13:11 0 删除 编辑
就像在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    107
  • 访问量
    386026