rdhaese
10/11/2017 - 6:59 AM

Foreign key without index: create CREATE queries

Script that checks for foreign keys without an index and creates CREATE queries for those indexes, following a common naming convention.

SELECT c.owner,
a.constraint_name, 
A.table_name, 
A.column_name,
'CREATE INDEX ' || a.constraint_name || '_IDX ON ' || A.table_name || ' (' || A.column_name || ');' INDEX_DDL
FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C 
WHERE A.owner = 'SCHEMA_NAME'
AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
--AND A.table_name='TABLE NAME' (use for checking single table) 
AND C.CONSTRAINT_TYPE = 'R'
and not exists ( select 1 
FROM ALL_INDEXES AI, ALL_IND_COLUMNS AIC
WHERE AI.TABLE_NAME = A.table_name
AND AIC.INDEX_NAME = AI.INDEX_NAME
AND AIC.TABLE_NAME = AI.TABLE_NAME
AND AIC.COLUMN_NAME = A.COLUMN_NAME
)