Shulou(Shulou.com)06/02 Report--

-- executed by the ZLHIS user

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 'Department Table _ PK' And b.r_Constraint_Name = c.Constraint_Name And

C.Table_Name Not In (Select table name 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 table name From zlBakTables Union All Select table name From Zlbigtables) Or Table_Name Like'% patient%') 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' department table _ 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.