Systems Engineering and RDBMS

Un-Indexed Foreign Keys

Posted by decipherinfosys on May 23, 2007

Having foreign keys in the system that are not indexed can lead to not only performance issues but also locking issues. We had briefly covered this in one of our previous blog post. In the event of an un-indexed Fk, if you are updating the parent table’s PK columns (very rare and is always a bad practice) or if you are deleting the parent table record, the child table will get locked. The performance also takes a nose dive for the joins if the FK column(s) is not indexed and there is no meaningful criteria on that table to filter upon. In the event of the FK column(s) being indexed, it could use that index to dig into the rows and filter them down to a smaller sub-set.

So, how can one easily identify the FK’s that are not indexed in the system? Oracle Guru Tom Kyte had posted this code on his web-site:

http://asktom.oracle.com/tkyte/unindex/unindex.sql

And based on the same lines, one can do this in the case of SQL Server (code written and tested in SQL 2005):

SELECT
CASE WHEN B.TABLE_NAME IS NULL THEN ‘******’ ELSE ‘OK’ END AS FK_STATUS,
A.TABLE_NAME, A.TAB_COLUMNS, B.TAB_COLUMNS
FROM
(select
a.table_name,
a.constraint_name,
max(case c.ordinal_position when 1 then c.column_name else null end) +
coalesce(max(case c.ordinal_position when 2 then ‘, ‘ + c.column_name else null end) , ”) +
coalesce(max(case c.ordinal_position when 3 then ‘, ‘ + c.column_name else null end) , ”) +
coalesce(max(case c.ordinal_position when 4 then ‘, ‘ + c.column_name else null end) , ”) +
coalesce(max(case c.ordinal_position when 5 then ‘, ‘ + c.column_name else null end) , ”) +
coalesce(max(case c.ordinal_position when 6 then ‘, ‘ + c.column_name else null end) , ”) +
coalesce(max(case c.ordinal_position when 7 then ‘, ‘ + c.column_name else null end) , ”)
as tab_columns
from
information_schema.constraint_column_usage a,
information_schema.TABLE_CONSTRAINTS b,
information_schema.key_column_usage c
where a.constraint_name = b.constraint_name
and b.constraint_type = ‘Foreign Key’
and a.table_name = c.table_name
and a.column_name = c.column_name
group by a.table_name, a.constraint_name) AS A
LEFT OUTER JOIN
(select object_name(b.object_id) as table_name, a.name,
max(case b.index_column_id when 1 then index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) +
coalesce(max(case b.index_column_id when 2 then ‘, ‘ + index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) , ”) +
coalesce(max(case b.index_column_id when 3 then ‘, ‘ + index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) , ”) +
coalesce(max(case b.index_column_id when 4 then ‘, ‘ + index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) , ”) +
coalesce(max(case b.index_column_id when 5 then ‘, ‘ + index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) , ”) +
coalesce(max(case b.index_column_id when 6 then ‘, ‘ + index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) , ”) +
coalesce(max(case b.index_column_id when 7 then ‘, ‘ + index_col(object_name(b.object_id), b.index_id, b.index_column_id) else null end) , ”)
as tab_columns
from sys.indexes a
inner join sys.index_columns b
on object_name(a.object_id) = object_name(b.object_id)
and a.index_id = b.index_id
where a.type in (1, 2 /*, 3 — for XML index */)
group by object_name(b.object_id), a.name) AS B
on A.table_name = B.table_Name
and B.tab_columns LIKE A.tab_columns + ‘%’
ORDER BY FK_STATUS
GO

This SQL code will provide you with an output of the FK constraints that are not indexed in your system. Output will look something like this (dummy tables used for illustration):

FK_STATUS TABLE_NAME TAB_COLUMNS TAB_COLUMNS
******              TABLE_A          COL1                      NULL
******              TABLE_B          COLX                      NULL
OK                    TABLE_C          COL1                       COL1,  COL2
OK                     TABLE_D         COL10                    COL10

and likewise for other tables. The ones with the “******” are the ones that need to be evaluated on a case by case basis.

One Response to “Un-Indexed Foreign Keys”

  1. […] Here we have also created index on the foreign key columns. It is very vital that foreign keys are always indexed. To identify un-indexed foreign keys and what happens, if they are not indexed, please look at our previous blog post https://decipherinfosys.wordpress.com/2007/05/23/un-indexed-foreign-keys/. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: