List of missing index for foreign key constraint.
--- a/oracle.rst Thu Jan 25 14:51:13 2018 +0200
+++ b/oracle.rst Fri Feb 09 21:02:34 2018 +0200
@@ -165,6 +165,38 @@
join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME
where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME like '%/_OLD' escape '/';
+List of missing index for foreign key constraint::
+
+ select
+ case when b.table_name is null then 'unindexed' else 'indexed' end as status,
+ a.table_name as table_name,
+ a.constraint_name as fk_name,
+ a.fk_columns as fk_columns,
+ b.index_name as index_name,
+ b.index_columns as index_columns
+ from (
+ select
+ a.table_name,
+ a.constraint_name,
+ listagg(a.column_name, ',') within group (order by a.position) fk_columns
+ from user_cons_columns a, user_constraints b
+ where
+ a.constraint_name = b.constraint_name
+ and b.constraint_type = 'R'
+ and a.owner = b.owner
+ group by a.table_name, a.constraint_name
+ ) a, (
+ select
+ table_name,
+ index_name,
+ listagg(c.column_name, ',') within group (order by c.column_position) index_columns
+ from user_ind_columns c
+ group by table_name, index_name
+ ) b
+ where a.table_name = b.table_name(+)
+ and b.index_columns(+) like a.fk_columns || '%'
+ order by 1 desc, 2;
+
List of current constraints limited to current user::
select * from USER_CONSTRAINTS;