Indentify unindexed Foreign Key columns

The following statement lists unindexed foreign key columns. The statement covers single as well as multi-column foreign key.

undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
column fk_columns format a30
column index_name format a30
column index_columns format a30

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
             dba_cons_columns   a
            ,dba_constraints    b
      where
             a.constraint_name  = b.constraint_name
      and    b.constraint_type  = 'R'
      and    a.owner            = '&&schema_owner'
      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
             dba_ind_columns  c
      where  c.index_owner    = '&&schema_owner'
      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;
 
Save the above statement in a file, identify_unindexed_foreign_keys.sql. 
Now test:

SQL> create table t1 (a number, b number);

Table created.

SQL> alter table t1 add constraint t1_pk primary key (a,b);

Table altered.

SQL> create table t2 (a number, b number, c number);

Table created.

SQL> alter table t2 add constraint t2_fk foreign key (a,b)
  2  references t1(a,b);

Table altered.

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
unindexed  T2         T2_FK   A,B 

SQL> create index idx_t2 on t2 (a,b);

Index created.

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
indexed    T2         T2_FK   A,B        IDX_T2     A,B
The above statement does not however list indexes on composite foreign keys where the column order in the index differs to that of the foreign key constraint.

For example:

SQL> drop index idx_t2;

Index dropped.

SQL> create index idx_t2 on t2 (b,a);

Index created.

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
unindexed  T2         T2_FK   A,B


The statement requires some modification so as to list foreign keys as "indexed", regardless of the column order of the composite index.

the modified statement:

select
      case
        when i.index_name is not null then
          'indexed'
        else
          'unindexed'
       end                  as status
      ,c.table_name         as table_name
      ,c.constraint_name    as fk_name
      ,c.fk_columns         as fk_columns
      ,i.index_name         as index_name
      ,i.index_columns      as index_columns
from
    (
      select  a.table_name
             ,a.constraint_name
             ,listagg(b.column_name, ' ' )
              within group (order by column_name) as fk_columns
      from
              dba_constraints a
             ,dba_cons_columns b
      where
              a.constraint_name = b.constraint_name
      and     a.constraint_type = 'R'
      and     a.owner           = b.owner
      and     a.owner           = '&&schema_owner'
      group by
              a.table_name
             ,a.constraint_name
    ) c
left outer join
  (
    select  table_name
           ,index_name
           ,cr
           ,listagg(column_name, ' ')
            within group (order by column_name) as index_columns
    from
        (
          select
                  table_name
                 ,index_name
                 ,column_position
                 ,column_name
                 ,connect_by_root(column_name) cr
          from
                  dba_ind_columns
          where
                  index_owner               = '&&schema_owner'
          connect
                 by prior column_position-1 = column_position
                 and prior index_name       = index_name
         )
    group by table_name
            ,index_name, cr
  ) i
      on c.fk_columns = i.index_columns
     and c.table_name = i.table_name;

the foreign key will now be shown as "indexed", once the script file has been modified :-)

SQL> @identify_unindexed_foreign_keys.sql
Enter value for schema_owner: USERNAME

STATUS     TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME INDEX_COLUMNS
---------- ---------- ------- ---------- ---------- -------------
indexed    T2         T2_FK   A,B        IDX_T2     A,B