Identifying missing FK indexes
Why publishing this under SQL instead of RDBMS Server?
The necessaty to create supporting indexes for foreign keys has been explained in other articles.
However writing a foolproof query to identify missing FK indexes is not so straightforward as it might look. I used to do it PLSQL, but this has proven not to scale very well and taking quite some elapsed time and system resources in databases with many thousands of tables. The blog entry is about the challenge to get the job done with a well performing SQL statement.
Problem description:
The statement here below is what I currently use to get the job done.
In contradiction to most solutions published on the web, it also handles multi-column FKs.
It does what it should do reasonably well, but I'm not yet completely happy with it. It still has a minor flaw that pops up in rare cases.
Multi-column FKs supported by an index with the same number of columns in any random order are recognized, for example a constraint on columns (A,B,C) supported by an index on (B,C,A).
Multi-column FKs supported by an index having more columns than the constraint are only recognized if the ordering of the leading index columns matches with the constraint column order, for example a FK constraint on columns (A,B) supported by an index on columns (A,B,C), but a constraint on columns (A,B) supported by an index on columns (B,A,C) isn't recognized and (incorrectly) included in the result set of the select statement.
Challenge:
I tried to fix this issue, but this appeared to be quite a challenge, and so far I didn't succeed.
So my question: are there any SQL gurus prepared to take up this challenge?
-- DEFINE &TableOwnerPattern = &1 -- DEFINE &TableNamePattern = &2 -- COLUMN owner FORMAT A25 HEADING 'Owner' -- COLUMN table_name FORMAT A30 HEADING 'Table' -- COLUMN constraint_name FORMAT A30 HEADING 'FK constraint' -- COLUMN column_list FORMAT A62 HEADING 'FK columns(s)' WORD WRAP WITH constr AS ( SELECT c.owner, c.table_name, c.constraint_name, listagg(col.column_name, ', ') within group (ORDER BY col.column_name) column_list_alphabetic, listagg(col.column_name, ', ') within group (ORDER BY col.position) column_list FROM dba_constraints c JOIN dba_cons_columns col ON ( col.owner = c.owner AND col.constraint_name = c.constraint_name AND col.table_name = c.table_name ) WHERE c.constraint_type = 'R' AND c.owner like '&TableOwnerPattern' AND c.table_name like '&TableNamePattern' GROUP BY c.owner, c.table_name, c.constraint_name ), idx AS ( SELECT table_owner, table_name, listagg(column_name, ', ') within group (ORDER BY column_name) column_list_alphabetic, listagg(column_name, ', ') within group (ORDER BY column_position) column_list FROM dba_ind_columns WHERE table_owner like '&TableOwnerPattern' AND table_name like '&TableNamePattern' GROUP BY table_owner, table_name, index_owner, index_name ) SELECT constr.owner, constr.table_name, constr.constraint_name, constr.column_list FROM constr WHERE NOT EXISTS ( SELECT 1 FROM idx WHERE constr.owner = idx.table_owner AND constr.table_name = idx.table_name AND ( idx.column_list_alphabetic = constr.column_list_alphabetic OR instr(idx.column_list,constr.column_list) = 1 ) );
- MarcMartens's blog
- Log in to post comments
Comments
Identifying missing FK indexes - UNindexed FK's
You can try my attempt at: http://www.bat-or-soft.com/?p=151
I supplied scripts for both Oracle and MS SQL Server
Michael
Script that displays the child tables that are missing indexes