Foreign keys and missing indexes

I

I think it is good idea to have foreign keys and also have matching indexes. Following script will take care of indexes you missed. It will show you which indexes are missing even for compound foreign keys. But you should check existing indexes just to be sure you are not creating useless index.

WITH f AS (
    SELECT
        t.table_name,
        t.constraint_name                                               AS index_name,
        LISTAGG(t.column_name, ', ') WITHIN GROUP (ORDER BY t.position) AS fk_cols
    FROM user_cons_columns t
    JOIN user_constraints n
        ON n.constraint_name    = t.constraint_name
    WHERE n.constraint_type     = 'R'
    GROUP BY t.table_name, t.constraint_name
)
SELECT
    f.table_name,
    f.index_name,
    f.fk_cols,
    i.index_name    AS existing_index,
    i.cols          AS index_cols,
    --
    'CREATE INDEX ' || RPAD(f.index_name, 30) ||
        ' ON ' || RPAD(f.table_name, 30) || ' (' || f.fk_cols || ') COMPUTE STATISTICS;' AS fix
FROM f
LEFT JOIN (
    SELECT i.table_name, i.index_name, LISTAGG(i.column_name, ', ') WITHIN GROUP (ORDER BY i.column_position) AS cols
    FROM user_ind_columns i
    GROUP BY i.table_name, i.index_name
) i
    ON i.table_name     = f.table_name
    AND i.cols          LIKE f.fk_cols || '%'
WHERE i.index_name      IS NULL  -- show only missing indexes
ORDER BY 1, 2;

Show indexes with columns:

SELECT
    i.table_name,
    i.index_name,
    LISTAGG(i.column_name, ', ') WITHIN GROUP (ORDER BY i.column_position) AS cols
FROM user_ind_columns i
--WHERE i.table_name LIKE '&TABLE_NAME'
GROUP BY i.table_name, i.index_name
ORDER BY i.table_name, i.index_name;