163 |
163 |
164 select * from SYS.USER_CONSTRAINTS cons |
164 select * from SYS.USER_CONSTRAINTS cons |
165 join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME |
165 join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME |
166 where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME like '%/_OLD' escape '/'; |
166 where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME like '%/_OLD' escape '/'; |
167 |
167 |
|
168 List of missing index for foreign key constraint:: |
|
169 |
|
170 select |
|
171 case when b.table_name is null then 'unindexed' else 'indexed' end as status, |
|
172 a.table_name as table_name, |
|
173 a.constraint_name as fk_name, |
|
174 a.fk_columns as fk_columns, |
|
175 b.index_name as index_name, |
|
176 b.index_columns as index_columns |
|
177 from ( |
|
178 select |
|
179 a.table_name, |
|
180 a.constraint_name, |
|
181 listagg(a.column_name, ',') within group (order by a.position) fk_columns |
|
182 from user_cons_columns a, user_constraints b |
|
183 where |
|
184 a.constraint_name = b.constraint_name |
|
185 and b.constraint_type = 'R' |
|
186 and a.owner = b.owner |
|
187 group by a.table_name, a.constraint_name |
|
188 ) a, ( |
|
189 select |
|
190 table_name, |
|
191 index_name, |
|
192 listagg(c.column_name, ',') within group (order by c.column_position) index_columns |
|
193 from user_ind_columns c |
|
194 group by table_name, index_name |
|
195 ) b |
|
196 where a.table_name = b.table_name(+) |
|
197 and b.index_columns(+) like a.fk_columns || '%' |
|
198 order by 1 desc, 2; |
|
199 |
168 List of current constraints limited to current user:: |
200 List of current constraints limited to current user:: |
169 |
201 |
170 select * from USER_CONSTRAINTS; |
202 select * from USER_CONSTRAINTS; |
171 |
203 |
172 List of constraints available to user:: |
204 List of constraints available to user:: |