Jonathan Lewis talking about the foreign key indexing issue in OTN. It might be that not all foreign keys need an index in your schema.
11.2 new function listagg is useful also in unindex. Here is a listagg version of unindex for the new Oracle version.
select case when i.index_name is not null then 'OK' else '****' end ok , c.table_name , c.constraint_name , c.cols , i.index_name from ( select a.table_name , a.constraint_name , listagg(b.column_name, ' ' ) within group (order by column_name) cols from user_constraints a, user_cons_columns b where a.constraint_name = b.constraint_name and a.constraint_type = 'R' 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) cols from ( select table_name , index_name , column_position , column_name , connect_by_root(column_name) cr from user_ind_columns connect by prior column_position-1 = column_position and prior index_name = index_name ) group by table_name, index_name, cr ) i on c.cols = i.cols and c.table_name = i.table_name ;
Even thou unindex query is not the kind of query that is run several times a day, I measured execution times from different versions. The test schema contains 1700 foreign keys. Performance comparison
"col_cnt > ALL" 13 sec "connect by" 3 sec "listagg" 1 sec
Foreign keys may point also to and from another schema. Here you can find a version using ALL_CONSTRAINTS and ALL_CONS_COLUMNS views.