Fixing index names

M

My personal preferrence is to match index names with constraint names. At least with primary keys, foreign keys and unique constraints. That way I know the purpose of the index just from its name resp. prefix.

This script suggests changes for unique constraints (including PK):

WITH i AS (
    SELECT
        d.table_name,
        d.index_name,
        LISTAGG(d.column_name, ', ') WITHIN GROUP (ORDER BY d.column_position) AS cols
    FROM user_ind_columns d
    JOIN user_indexes i
        ON i.index_name     = d.index_name
    WHERE i.uniqueness      = 'UNIQUE'
    GROUP BY d.table_name, d.index_name
),
p AS (
    SELECT
        c.table_name,
        c.constraint_name,
        LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols
    FROM user_cons_columns c
    JOIN user_constraints n
        ON n.constraint_name    = c.constraint_name
        AND n.constraint_type   IN ('P', 'U')
    GROUP BY c.table_name, c.constraint_name
)
SELECT
    p.table_name,
    p.constraint_name,
    p.cols                  AS table_cols,
    i.index_name,
    i.cols                  AS index_cols,
    --
    'ALTER INDEX ' || RPAD(i.index_name, 30) ||
        ' RENAME TO ' || p.constraint_name || ';' AS fix
FROM i
JOIN p
    ON p.table_name         = i.table_name
    AND p.cols              = i.cols
    AND p.constraint_name   != i.index_name
ORDER BY 1, 2;

This script suggests changes for foreign keys:

WITH i AS (
    SELECT
        d.table_name,
        d.index_name,
        LISTAGG(d.column_name, ', ') WITHIN GROUP (ORDER BY d.column_position) AS cols
    FROM user_ind_columns d
    JOIN user_indexes i
        ON i.index_name     = d.index_name
    GROUP BY d.table_name, d.index_name
),
f AS (
    SELECT
        c.table_name,
        c.constraint_name,
        LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols
    FROM user_cons_columns c
    JOIN user_constraints n
        ON n.constraint_name    = c.constraint_name
        AND n.constraint_type   IN ('R')
    GROUP BY c.table_name, c.constraint_name
)
SELECT
    f.table_name,
    f.constraint_name,
    f.cols                  AS table_cols,
    i.index_name,
    i.cols                  AS index_cols,
    --
    'ALTER INDEX ' || RPAD(i.index_name, 30) ||
        ' RENAME TO ' || f.constraint_name || ';' AS fix
FROM f
JOIN i
    ON f.table_name         = i.table_name
    AND f.cols              = i.cols
    AND f.constraint_name   != i.index_name
WHERE i.index_name NOT IN (
    SELECT p.constraint_name
    FROM user_constraints p
    WHERE p.constraint_type     IN ('P', 'U')
)
ORDER BY 1, 2;