Find Foreign Keys Without Indexes

While there is a handful of cases where foreign keys without indexes might be useful and needed, more likely you will end up in a problem with unnecessary or plethora of locking operations or nonoptimized execution plans (missing nested loops, unnecessary full table scans, … ).

Double-check it yourself and make sure your DB is running fine 🙂

WITH w_col_constr AS (
                      SELECT cons_col.owner
                            ,cons_col.table_name
                            ,cons_col.constraint_name
                            ,listagg(cons_col.column_name, ';') within GROUP(ORDER BY cons_col.position) fk_columns
                      FROM dba_cons_columns cons_col
                      --
                      JOIN dba_constraints constr
                      ON cons_col.owner = constr.owner
                      AND cons_col.constraint_name = constr.constraint_name
                      AND constr.constraint_type = 'R' -- R = Referential /such as FK/ (O = ReadOnly on a view; P = Primary Key; C = Check on a table; U = Unique Key; V = Check on a view)
                      --
                      WHERE 1=1
                      AND cons_col.owner = 'ORACLE_WORLD'
                      --
                      GROUP BY cons_col.owner
                              ,cons_col.table_name
                              ,cons_col.constraint_name
                     )
, w_ind_col AS (
                SELECT ind_col.index_owner AS owner
                      ,ind_col.table_name
                      ,ind_col.index_name
                      ,listagg(ind_col.column_name, ';') within GROUP(ORDER BY ind_col.column_position) index_columns
                FROM dba_ind_columns ind_col
                --
                WHERE 1 = 1
                AND ind_col.index_owner = 'ORACLE_WORLD'
                --
                GROUP BY ind_col.index_owner
                        ,ind_col.table_name
                        ,ind_col.index_name
                )

SELECT cc.owner
      ,cc.table_name AS table_name
      ,cc.constraint_name AS fk_name
      ,cc.fk_columns AS fk_column_list
      ,ic.index_name AS idx_name
      ,ic.index_columns AS idx_column_list
FROM w_col_constr cc
--
LEFT JOIN w_ind_col ic
ON cc.owner = ic.owner
AND cc.table_name = ic.table_name
AND ic.index_columns LIKE cc.fk_columns || '%'
--
WHERE 1 = 1
AND ic.table_name IS NULL -- no index
--
ORDER BY 1, 2, 3