Script to Dynamically Create Missing Foreign Key Indexes
--
-- Dynamically Create Missing Foreign key Indexes
--
 
select 'create index &&schema_owner..FK_'||TABLE_NAME||' on &&schema_owner..'||table_name||'('||FK_COLUMNS||') tablespace &tablespace_name initrans 48;'  from (
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
   )
   where status = 'unindexed'
/


Published 27th September 2022

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License