Finding Unindexed Foreign Keys in Oracle, now with index creation DDL!

2025-08-13 Update: It’s now even better, use this post instead: https://carsandcode.com/2025/08/13/finding-unindexed-foreign-keys-in-oracle-now-with-even-better-index-creation-ddl/

Previously I wrote a better query for finding unindexed foreign key indexes. Today Lance (one of the great folks at Insum) and I went through the query in a code review and we came up with a few improvements.

The first thing that bothered me was the ‘possible missing index’ result. I did some testing, and I couldn’t come up with a situation where I’d get a missing index result, but the index really was there, so I made the results just say “Missing” if I couldn’t find the index.

Lance and I also cleaned up a few column names to make things easier to understand. For example, I previously had a column named foreign_key_index that had either “Exists” or “Missing” which really wasn’t a foreign_key_index, but instead a flag showing the status of the foreign key index. This was renamed to “index_existence”. Other columns got better names too.

Lance made a suggestion to take the final query and make it a CTE (Common Table Expression, sometimes called a “with clause”), and then create a new simple final query that just selected each column name from the CTE making it very easy for folks to comment out columns if they are not interested in them, or to re-order the columns if they wanted.

We also added another column to the report with the DDL that would create an index that would eliminate the missing foreign key index.

Some notes on that DDL statement:

• I’m a very big fan of naming things what they are so my index name ends up being quite long. You’ll almost certainly need to have long object names enabled in your database if you want to use the index name as is. If you need a shorter name, you can just modify the query.
• If the query returns multiple foreign key indexes to create on a single table, take a look and see if one of the multicolumn indexes would also eliminate the need for some of the indexes that have fewer columns. Remember though, that the columns need to be in the right order. So if your table has a foreign key constraint on columns c1, c2, c3, and c4 that points to one table and then another foreign key constraint just on only c4 that points to another table, an index on c1, c2, c3, and c4 in that order will be able to be used for foreign key constraint number one, but not for foreign key constraint number two.
• I always use lowercase to write all my statements as science says that this is easier for humans to read. All lowercase letters are also much easier to write. When my statements are run, the data dictionary is always populated in uppercase because I never double-quote any of the schema, table, or column names. Some people (and this is never recommended) actually double-quote table and column names and put names in lowercase or mixed-case in the data dictionary which means that every statement that accesses those objects must reference those objects as double-quoted identifiers. Amazingly you can also create schemas in lowercase too! I would never, never do this as you’re just making all your code harder to read and write. I debated on allowing for this craziness, but in the end decided that, yeah, it’s probably better to just help the misguided folks who do this so the owner, table name, and column name(s) in the DDL statement are all double-quoted identifiers as they are stored in the data dictionary. The DDL isn’t as pretty, but it works for more people.

The query could also be just a bit more efficient by eliminating the first column, index_existence. The DDL column now is effectively the same information. I left it because I think it adds some value and would make filtering a bit easier to read.

I’ve formatted the query below to be easy to read when displayed with at least 144 columns of width (see the image above). Below the code doesn’t look as pretty because there are not 144 columns of width in the text block.

To that end, here’s the “better” query:

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 )
        , foreign_key_index_query as ( select decode(ic.table_name, null, 'Missing'
                                                                        , 'Exists')                                   as index_existence
                                            , to_char(dbat.num_rows, '999,999,999,999,999')                           as last_analyzed_row_count
                                            , dbat.last_analyzed
                                            , cc.owner                                                                as table_owner
                                            , cc.table_name
                                            , constraint_name                                                         as foreign_key_name
                                            , constraint_column_list                                                  as foreign_key_column_list
                                            , coalesce(index_name, '*** Missing Index ***')                           as index_name
                                            , coalesce(index_column_list,'*** Missing Index ***')                     as index_column_list
                                            , decode(ic.table_name, null, 'create index '||lower(cc.table_name||'_foreign_key_index_'||
                                                                          replace(replace(constraint_column_list,',','_'),' '))||' on "'|| 
                                                                          cc.owner || '"."'||cc.table_name||'"("'||
                                                                          replace(replace(constraint_column_list,',','","'),' ')||'");'
                                                                        , '*** Supporting index already exists ***' ) as create_index_ddl
                                         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 || '%' ) )
  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
order by last_analyzed_row_count desc nulls last, table_owner, table_name, foreign_key_column_list;

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.

2024-03 Update: Jeff Smith gave the Insum team an overview of the SQL Developer extension for VSCode. His blog post shows how to add snippets to VSCode SQL Developer. I thought I’d give it a shot and created the following which works like a charm. Using “ffk”, for “Find Foreign Keys” will give you the above query if you put the following into your oracle_sql.json file.

"ffk": {
	"prefix": "ffk",
	"body": [
		"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 )",
		"        , foreign_key_index_query as ( select decode(ic.table_name, null, 'Missing'",
		"                                                                        , 'Exists')                                   as index_existence",
		"                                            , to_char(dbat.num_rows, '999,999,999,999,999')                           as last_analyzed_row_count",
		"                                            , dbat.last_analyzed",
		"                                            , cc.owner                                                                as table_owner",
		"                                            , cc.table_name",
		"                                            , constraint_name                                                         as foreign_key_name",
		"                                            , constraint_column_list                                                  as foreign_key_column_list",
		"                                            , coalesce(index_name, '*** Missing Index ***')                           as index_name",
		"                                            , coalesce(index_column_list,'*** Missing Index ***')                     as index_column_list",
		"                                            , decode(ic.table_name, null, 'create index '||lower(cc.table_name||'_foreign_key_index_'||",
		"                                                                          replace(replace(constraint_column_list,',','_'),' '))||' on \"'|| ",
		"                                                                          cc.owner || '\".\"'||cc.table_name||'\"(\"'||",
		"                                                                          replace(replace(constraint_column_list,',','\",\"'),' ')||'\");'",
		"                                                                        , '*** Supporting index already exists ***' ) as create_index_ddl",
		"                                         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 || '%' ) )",
		"  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",
		"order by last_analyzed_row_count desc nulls last, table_owner, table_name, foreign_key_column_list;"
	],
	"description": "Find unindexed foreign keys"
}