Foreign Key sem índice

A query abaixo lista todas as FKs existentes na instância que não tem índice na tabela filha:

with
  dados as
   (select
      cc.owner, cc.table_name, cc.constraint_name, cc.column_name, cc.position, i.index_name, i.column_position index_position, r.owner r_ownwer, r.table_name r_table_name,
      count(distinct cc.column_name) over(partition by cc.owner, cc.table_name, cc.constraint_name) qtde_cons_columns,
      count(distinct i.column_name) over(partition by cc.owner, cc.table_name, cc.constraint_name, i.index_name) qtde_ind_columns
    from dba_ind_columns i, dba_cons_columns cc, dba_constraints r, dba_constraints c
    where
      c.constraint_type = 'R' and
      r.owner = c.r_owner and
      r.constraint_name = c.r_constraint_name and
      cc.owner = c.owner and
      cc.constraint_name = c.constraint_name and
      i.table_owner (+) = cc.owner and
      i.table_name (+) = cc.table_name and
      i.column_name (+) = cc.column_name and
      i.column_position (+) = cc.position),
  cons_ok as
   (select distinct owner, constraint_name
    from dados
    where
      qtde_cons_columns = qtde_ind_columns) select * from dados where
  (owner, constraint_name) not in (select * from cons_ok) order by 1, 2, 3, 5, 6;

Colunas que são FK e não tem índice podem causar problemas de performance caso a query utilizada faça join através deste campo (o que geralmente ocorre, justamente por ser FK).



Mais informações sobre quando é necessário criar índices em FK's:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754