外來鍵缺索引檢查指令碼

xypincle發表於2017-03-24
索引指令碼

  1. --以ZLHIS使用者執行
  2. Select Child_Table, Foreign_Key,
  3.        Cname1 || Nvl2(Cname2, ',' || Cname2, Null) || Nvl2(Cname3, ',' || Cname3, Null) ||
  4.         Nvl2(Cname4, ',' || Cname4, Null) || Nvl2(Cname5, ',' || Cname5, Null) || Nvl2(Cname6, ',' || Cname6, Null) ||
  5.         Nvl2(Cname7, ',' || Cname7, Null) || Nvl2(Cname8, ',' || Cname8, Null) Columns, Main_Table, Primary_Key,
  6.        Decode(p.Table_Name, Null, 0, 1) Key_Rows
  7. From (Select Col_Cnt, Main_Table, Primary_Key, Child_Table, Foreign_Key,
  8.               Cname1 || Nvl2(Cname2, ',' || Cname2, Null) || Nvl2(Cname3, ',' || Cname3, Null) ||
  9.                Nvl2(Cname4, ',' || Cname4, Null) || Nvl2(Cname5, ',' || Cname5, Null) || Nvl2(Cname6, ',' || Cname6, Null) ||
  10.                Nvl2(Cname7, ',' || Cname7, Null) || Nvl2(Cname8, ',' || Cname8, Null) As Columns, Cname1, Cname2, Cname3,
  11.               Cname4, Cname5, Cname6, Cname7, Cname8
  12.        From (Select c.Table_Name As Main_Table, b.r_Constraint_Name As Primary_Key, b.Table_Name As Child_Table,
  13.                      b.Constraint_Name As Foreign_Key, Max(Decode(Position, 1, Column_Name, Null)) Cname1,
  14.                      Max(Decode(Position, 2, Column_Name, Null)) Cname2, Max(Decode(Position, 3, Column_Name, Null)) Cname3,
  15.                      Max(Decode(Position, 4, Column_Name, Null)) Cname4, Max(Decode(Position, 5, Column_Name, Null)) Cname5,
  16.                      Max(Decode(Position, 6, Column_Name, Null)) Cname6, Max(Decode(Position, 7, Column_Name, Null)) Cname7,
  17.                      Max(Decode(Position, 8, Column_Name, Null)) Cname8, Count(*) Col_Cnt
  18.               From User_Cons_Columns A, User_Constraints B, User_Constraints C
  19.               Where a.Constraint_Name = b.Constraint_Name And b.Status = 'ENABLED' And b.Constraint_Type = 'R' And
  20.                b.r_Constraint_Name <> '部門表_PK' And b.r_Constraint_Name = c.Constraint_Name And
  21.                c.Table_Name Not In (Select 表名 From zlBaseCode)
  22.               Group By c.Table_Name, b.Table_Name, b.Constraint_Name, b.r_Constraint_Name)) Cons, User_Tab_Statistics S,
  23.      (Select Table_Name
  24.        From User_Tables
  25.        Where Table_Name In (Select 表名 From zlBakTables Union All Select 表名 From Zlbigtables) Or Table_Name Like '%病人%') P
  26. Where Cons.Child_Table = s.Table_Name(+) And Cons.Main_Table = p.Table_Name(+) And Not Exists
  27.  (Select 1
  28.        From (Select Table_Name,
  29.                      Cname1 || Nvl2(Cname2, ',' || Cname2, Null) || Nvl2(Cname3, ',' || Cname3, Null) ||
  30.                       Nvl2(Cname4, ',' || Cname4, Null) || Nvl2(Cname5, ',' || Cname5, Null) ||
  31.                       Nvl2(Cname6, ',' || Cname6, Null) || Nvl2(Cname7, ',' || Cname7, Null) ||
  32.                       Nvl2(Cname8, ',' || Cname8, Null) As Columns
  33.               From (Select i.Table_Name, Max(Decode(Column_Position, 1, Column_Name, Null)) Cname1,
  34.                             Max(Decode(Column_Position, 2, Column_Name, Null)) Cname2,
  35.                             Max(Decode(Column_Position, 3, Column_Name, Null)) Cname3,
  36.                             Max(Decode(Column_Position, 4, Column_Name, Null)) Cname4,
  37.                             Max(Decode(Column_Position, 5, Column_Name, Null)) Cname5,
  38.                             Max(Decode(Column_Position, 6, Column_Name, Null)) Cname6,
  39.                             Max(Decode(Column_Position, 7, Column_Name, Null)) Cname7,
  40.                             Max(Decode(Column_Position, 8, Column_Name, Null)) Cname8
  41.                      From User_Ind_Columns I, User_Indexes A, User_Constraints B
  42.                      Where a.Index_Name = i.Index_Name And a.Status = 'VALID' And i.Table_Name = b.Table_Name And
  43.                            b.Constraint_Type = 'R' And b.r_Constraint_Name <> '部門表_PK'
  44.                      Group By i.Table_Name, i.Index_Name)) Inds
  45.        Where Instr(',' || Inds.Columns, ',' || Cons.Columns) = 1 And Cons.Child_Table = Inds.Table_Name)
  46. Order By Key_Rows Desc, s.Num_Rows Desc Nulls Last

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28878983/viewspace-2136043/，如需轉載，請註明出處，否則將追究法律責任。

相關文章