It all started here: https://carsandcode.com/2023/07/27/finding-unindexed-foreign-keys-in-oracle/
It got better here: https://carsandcode.com/2023/09/01/finding-unindexed-foreign-keys-in-oracle-now-with-index-creation-ddl/
But now, it’s even better!
Changes:
- A comment section at the front.
- Formatting changes.
- Better table aliases.
- Indexes are now owned by the table owner rather than the DBA running the script.
- All missing indexes are created in a single tablespace named “missing_foreign_key_indexes”. Obviously, this tablespace needs to exist for the statements to work. You might want to change this clause to use “your user’s index tablespaces”, and you should drop this if you are on Oracle Autonomous on Oracle Cloud, since you only get one tablespace for your stuff and everything will automatically go to it.
- There are suggestions for making shorter index names if needed/wanted.
- The “local” clause was added for partitioned tables.
- You can optionally require validated foreign keys, valid indexes, and visible indexes. Today’s invalid or invisible index can turn into tomorrow’s index, so I left the default to show all indexes.
- We are now sorting numerically.
- Change “select *” into “select username” for the not in clause that eliminates users.
- Added a where clause to find missing indexes by default.
Again, here’s the formatted code that doesn’t look great, but if you copy it, it should be formatted as it is in the above picture.
-- Created by Rich Soule of Talan's Oracle Group in collaboration with Lance Eaton.
--
-- Notes:
-- * Can ignore unusable/invisible/bitmap indexes; prefers NORMAL (and function-based NORMAL) b-trees
-- * Emits LOCAL for partitioned child tables
-- * change 'missing_foreign_key_indexes' tablespace to taste (or drop on Autonomous)
-- * if you want to shorten the index names, you can replace the two lines with comments below with something like: 'missing_fk_index'||rownum
-- * designed to be run by a DBA with access to the DBA views, but can also be run by a regular user by replacing the dba_ views with
-- all_ views or user_ views (search and replace dba_ with all_ or user_)
with owner_exclusion_list as ( select username from dba_users where oracle_maintained = 'Y'
union all select 'ORDS_METADATA' from dual
union all select 'ORDS_PUBLIC_USER' from dual )
, constraint_column_list as ( select owner
, table_name
, constraint_name
, listagg(column_name, ', ') within group (order by position) as constraint_column_list
from dba_cons_columns
join dba_constraints using (owner, table_name, constraint_name)
where constraint_type = 'R'
and status = 'ENABLED'
-- and validated = 'VALIDATED' -- uncomment to require validated fks
and owner not in (select username from owner_exclusion_list)
group by owner, table_name, constraint_name )
, index_column_list as ( select di.owner
, di.table_name
, di.index_name
, listagg(dic.column_name, ', ') within group (order by dic.column_position) as index_column_list
from dba_indexes di
join dba_ind_columns dic on (dic.index_owner = di.owner and dic.index_name = di.index_name)
where di.owner not in (select username from owner_exclusion_list)
-- and di.status = 'VALID' -- uncomment to require valid indexes
-- and di.visibility = 'VISIBLE' -- uncomment to require visible indexes
and di.index_type in ('NORMAL','FUNCTION-BASED NORMAL')
group by di.owner, di.table_name, di.index_name )
, foreign_key_index_query as (select decode(icl.table_name, null, 'Missing', 'Exists') as index_existence
, dt.num_rows as last_analyzed_row_count_number
, to_char(dt.num_rows, '999,999,999,999,999') as last_analyzed_row_count
, dt.last_analyzed
, ccl.owner as table_owner
, ccl.table_name
, ccl.constraint_name as foreign_key_name
, ccl.constraint_column_list as foreign_key_column_list
, coalesce(icl.index_name, '*** Missing Index ***') as index_name
, coalesce(icl.index_column_list, '*** Missing Index ***') as index_column_list
, decode(icl.table_name, null,'create index "'||ccl.owner||'".'||
lower(ccl.table_name||'_foreign_key_index_on_'|| -- Shorten these two lines to have
replace(replace(ccl.constraint_column_list,',','_'),' '))|| -- smaller index names
' on "'||ccl.owner||'"."'||ccl.table_name||'"('||
replace(replace(ccl.constraint_column_list,',','","'),' ')||')'||
decode(dt.partitioned, 'YES', ' local', '')||
' tablespace missing_foreign_key_indexes;'
,'*** supporting index already exists ***') as create_index_ddl
from constraint_column_list ccl
join dba_tables dt on (dt.owner = ccl.owner and dt.table_name = ccl.table_name)
left join index_column_list icl on ( icl.owner = ccl.owner and icl.table_name = ccl.table_name
and icl.index_column_list like ccl.constraint_column_list || '%' ))
select index_existence
, last_analyzed_row_count
, last_analyzed
, table_owner
, table_name
, foreign_key_name
, foreign_key_column_list
, index_name
, index_column_list
, create_index_ddl
from foreign_key_index_query
where index_existence = 'Missing' -- comment to see both Exists & Missing
order by last_analyzed_row_count_number desc nulls last, table_owner, table_name, foreign_key_column_list;
Cars and Code 