# HG changeset patch
# User Oleksandr Gavenko <gavenkoa@gmail.com>
# Date 1518202954 -7200
# Node ID f643c573f870c024defc7e745c7d2b12088d21a7
# Parent  f991bbf12a59f6593037665b0b3ade32eae8f4d8
List of missing index for foreign key constraint.

diff -r f991bbf12a59 -r f643c573f870 oracle.rst
--- 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;