Identifying missing FK indexes

articles: 

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 )
                  );

Comments

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

The following sql takes the owner as input.

The putput row with the "****" identifies that the SNAP_SHOT_HEADER_CQSDTL_REF table is missing an index on the BOM_INFORMATION_KEY column.

SQL > @report_missing_Indexes_on_fko.sql vqa

STATUS       TABLE_NAME                     COLUMNS              COLUMNS_INDEXED
------------ ------------------------------ -------------------- -------------------------------------------------------------
ok           BOM_INFORMATION                SNAPSHOT_KEY         SNAPSHOT_KEY, BOM_INFORMATION_KEY, CHLD
ok           BOM_INFORMATION                SNAPSHOT_KEY         SNAPSHOT_KEY, BOM_INFORMATION_KEY
ok           BOM_INFORMATION                SNAPSHOT_KEY         SNAPSHOT_KEY, BOM_INFORMATION_KEY, FINISHED_GOOD_NUMBER, CHLD
ok           BOM_INFORMATION                SNAPSHOT_KEY         SNAPSHOT_KEY, BOM_INFORMATION_KEY, CHLD, SANDBOX_CHILD_ID
****         SNAP_SHOT_HEADER_CQSDTL_REF    BOM_INFORMATION_KEY

Report_missing_Indexes_on_fko.sql looks like the following:
set lines 200
set pages 50
set wrap off
column columns format a20 word_wrapped
column table_name format a30 word_wrapped
select decode( b.table_name, NULL, '****', 'ok' ) Status, 
	   a.table_name, a.columns, b.columns columns_indexed
from 
( select substr(a.table_name,1,30) table_name, 
		 substr(a.constraint_name,1,30) constraint_name, 
	     max(decode(position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from all_cons_columns a, all_constraints b
   where a.owner=upper('&1')
     and b.owner=upper('&1')
     and a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name, 
	     max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
    from all_ind_columns 
    where TABLE_OWNER=upper('&1')
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
/