外來鍵缺索引檢查指令碼
-
--以ZLHIS使用者執行
-
Select Child_Table, Foreign_Key,
-
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, Main_Table, Primary_Key,
-
Decode(p.Table_Name, Null, 0, 1) Key_Rows
-
From (Select Col_Cnt, Main_Table, Primary_Key, Child_Table, Foreign_Key,
-
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) As Columns, Cname1, Cname2, Cname3,
-
Cname4, Cname5, Cname6, Cname7, Cname8
-
From (Select c.Table_Name As Main_Table, b.r_Constraint_Name As Primary_Key, b.Table_Name As Child_Table,
-
b.Constraint_Name As Foreign_Key, 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 User_Cons_Columns A, User_Constraints B, User_Constraints C
-
Where a.Constraint_Name = b.Constraint_Name And b.Status = 'ENABLED' And b.Constraint_Type = 'R' And
-
b.r_Constraint_Name <> '部門表_PK' And b.r_Constraint_Name = c.Constraint_Name And
-
c.Table_Name Not In (Select 表名 From zlBaseCode)
-
Group By c.Table_Name, b.Table_Name, b.Constraint_Name, b.r_Constraint_Name)) Cons, User_Tab_Statistics S,
-
(Select Table_Name
-
From User_Tables
-
Where Table_Name In (Select 表名 From zlBakTables Union All Select 表名 From Zlbigtables) Or Table_Name Like '%病人%') P
-
Where Cons.Child_Table = s.Table_Name(+) And Cons.Main_Table = p.Table_Name(+) And Not Exists
-
(Select 1
-
From (Select 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) As Columns
-
From (Select i.Table_Name, Max(Decode(Column_Position, 1, Column_Name, Null)) Cname1,
-
Max(Decode(Column_Position, 2, Column_Name, Null)) Cname2,
-
Max(Decode(Column_Position, 3, Column_Name, Null)) Cname3,
-
Max(Decode(Column_Position, 4, Column_Name, Null)) Cname4,
-
Max(Decode(Column_Position, 5, Column_Name, Null)) Cname5,
-
Max(Decode(Column_Position, 6, Column_Name, Null)) Cname6,
-
Max(Decode(Column_Position, 7, Column_Name, Null)) Cname7,
-
Max(Decode(Column_Position, 8, Column_Name, Null)) Cname8
-
From User_Ind_Columns I, User_Indexes A, User_Constraints B
-
Where a.Index_Name = i.Index_Name And a.Status = 'VALID' And i.Table_Name = b.Table_Name And
-
b.Constraint_Type = 'R' And b.r_Constraint_Name <> '部門表_PK'
-
Group By i.Table_Name, i.Index_Name)) Inds
-
Where Instr(',' || Inds.Columns, ',' || Cons.Columns) = 1 And Cons.Child_Table = Inds.Table_Name)
- Order By Key_Rows Desc, s.Num_Rows Desc Nulls Last
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28878983/viewspace-2136043/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 檢查外來鍵是否有索引的指令碼索引指令碼
- 查詢沒有索引的外來鍵索引
- oracle查詢表資訊(索引,外來鍵,列等)Oracle索引
- 關於外來鍵約束和對應主鍵資訊的查詢指令碼指令碼
- RAC指令碼檢查指令碼
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引
- Oracle 外來鍵查詢sqlOracleSQL
- ORACLE: 查詢(看)表的主鍵、外來鍵、唯一性約束和索引Oracle索引
- Oracle根據主鍵查詢外來鍵Oracle
- 表外來鍵未加索引之處理索引
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- oracle檢視當前使用者下所有外來鍵、主鍵、索引、sequence的建立語句Oracle索引
- 記憶體檢查指令碼記憶體指令碼
- MySQL:slave 延遲一列 外來鍵檢查和自增加鎖MySql
- 查詢一個表的外來鍵
- 用shell指令碼來給mysql加索引指令碼MySql索引
- 【SCN】Oracle檢查scn值指令碼Oracle指令碼
- Linux系統檢查指令碼Linux指令碼
- 結構損壞檢查指令碼指令碼
- process不釋放,檢查指令碼指令碼
- 外來鍵沒有索引哪些DML操作會被阻塞索引
- 【fk_index】外來鍵中有無索引的區別Index索引
- SQL SERVER中找出拙劣的約束,索引,外來鍵SQLServer索引
- dataguard 手動切換,檢查指令碼指令碼
- 檢查備份情況的指令碼指令碼
- oracle外來鍵索引解決父表鎖定問題Oracle索引
- 外來鍵有無索引帶來的影響學習與測試索引
- Oracle查詢表的外來鍵引用關係Oracle
- 【MySQL】MySQL進階(外來鍵約束、多表查詢、檢視、備份與恢復)MySql
- 約束:確保資料的完整性(主鍵,唯一,檢查,預設,非空,外來鍵)
- sqlserver外來鍵SQLServer
- 資料庫的常規檢查指令碼資料庫指令碼
- 巧用shell生成資料庫檢查指令碼資料庫指令碼
- 資料庫的檢查步驟指令碼資料庫指令碼
- oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- 每天檢查正式Server的一個指令碼Server指令碼
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- 批量修改欄位長度,考慮主鍵外來鍵索引的情況【轉】索引