Update: There is a new better version of the query here: https://carsandcode.com/2023/09/01/finding-unindexed-foreign-keys-in-oracle-now-with-index-creation-ddl/
I’ve always been a bit unhappy with most (all?) of the queries you find when searching for “Oracle unindexed foreign keys query” on Google. Certainly, the ones that don’t use listagg to aggregate the constraint columns and the index columns are insane. I realize those queries might have been around since before listagg was available, but listagg was introduced in 11gR2 which was released in 2009.
To that end, the following 33 line query will find all the possibly unindexed foreign key constraints in your database. Since the matching happens on the column names, if you have a difference in column names between the constraint columns and the index columns, you won’t get a match and it will show up in the results as ‘Could be missing’. 99.99 times out of a hundred the index is missing.
I’ve also added the number of rows and last analyzed date from DBA_TABLES (remember, this number of rows may not be accurate as the value is only updated when the table is analyzed) so that the biggest possible offenders would be the first results. Even if the number of rows is small and the statistics are up to date, you’ll pretty much always want to have that foreign key index to eliminate over-locking the detail rows when a master row is updated. In all my years I’ve yet to run across a business application where the ‘index caused too much overhead’ and needed to be removed. I’m not saying those situations don’t exist, but they are exceedingly rare.
If you want to see how Oracle is doing with its schemas, you could change the owner_exclusion_list CTE (Common Table Expression, sometimes called a WITH clause) to something like “select ‘nobody’ from dual’). Right now that CTE gets the list of schemas that are Oracle maintained and adds in the ORDS_METADA and ORDS_PUBLIC_USER schemas which are, for some reason, not marked as Oracle maintained.
Using an APEX page to display the results of the query also allows me to format the last analyzed date using APEX’s “since” formatting, which gives a really good indication of possible stale statistics.
Update: Again, there is a new, better version of this query here: https://carsandcode.com/2023/09/01/finding-unindexed-foreign-keys-in-oracle-now-with-index-creation-ddl/
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_columns 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' -- R = Referential Foreign Key Constraint
and owner not in (select * from owner_exclusion_list)
group by owner, table_name, constraint_name )
, index_columns as ( select index_owner as owner
, table_name
, index_name
, listagg(column_name, ', ') within group(order by column_position) as index_column_list
from dba_ind_columns
where index_owner not in (select * from owner_exclusion_list)
group by index_owner, table_name, index_name )
select decode(ic.table_name, null, 'Could be missing'
, 'Exists' ) as foreign_key_index
, to_char(dbat.num_rows, '999,999,999,999,999,999') as last_analyzed_row_count
, dbat.last_analyzed
, cc.owner
, cc.table_name
, constraint_name as foreign_key_constraint_name
, constraint_column_list as foreign_key_column_list
, coalesce(index_name, '*** Possible Missing Index ***') as index_name
, coalesce(index_column_list,'*** Possible Missing Index ***') as index_column_list
from constraint_columns cc
join dba_tables dbat on ( dbat.owner = cc.owner and dbat.table_name = cc.table_name)
left join index_columns ic on ( cc.owner = ic.owner and cc.table_name = ic.table_name
and ic.index_column_list like cc.constraint_column_list || '%' )
order by dbat.num_rows desc nulls last, cc.owner, cc.table_name, constraint_column_list;
Update: And again, there is a new better version of this query here: https://carsandcode.com/2023/09/01/finding-unindexed-foreign-keys-in-oracle-now-with-index-creation-ddl/
Let me know if this query is helpful, and happy DBAing/Developing!
Note: On my Twitter post about this blog, I took a screenshot and put in the alt text for the image with the username column in the first CTE as ‘username’ instead of just username. This means that the Oracle maintained schemas are NOT excluded anymore. I was testing on a database that only had a single schema and wanted to include the Oracle schemas too. I also left off the owner column for the list in the final query.
2024-02 Update: As my friend Anton Nielsen pointed out, not everybody has DBA privs… If you don’t, just do a search and replace of “dba_” with “all_”. Then you can run this as a non-privileged user and you’ll be sure to capture missing indexes in your own schema and any other schema you can see.
Cars and Code 