Raghu On Tech

Pro tips on managing data at scale

How To Find Missing Indexes On Foreign Key Columns ?

by | Jul 10, 2018 | 1 comment

Problem Statement:

You are trying to figure out if there are any Foreign key constraints in your database with out the corresponding Index. If you are working in an OLTP environment you already know most of your query patterns include a join on primary key (parent table) and foreign key(s) (child tables). It is important to know and address any of these missing indexes in development/lower environments before they become a problem in production database environments. As basic as it may sound, I have discovered these missing indexes once in a while.



Solution is an easy one. You run the below SQL and get the list of Foreign Key constraints with out the corresponding indexes. Once you identify these missing indexes you need to use your application/business knowledge to ask yourself if you need to create these indexes. If you are working with a conventional application, answer to this question will most probably be yes. If you are working with an edge case application where parents don’t talk to children then you got a weird family ūüôā


When I run this SQL against my database I get the below output, which tells me that DB2 explain tables do not have corresponding indexes for their foreign keys. This might not be that big of a problem if your explain tables do not grow.

However if you are keeping track of say for e.g. your top 10 SQL statements performance over a 5 year period of time and you save their explain information into these explain tables, then you may need to create indexes to avoid performance problems for any reporting that you may perform on these tables.



Hope you enjoyed this short blog post. If you have any similar SQL tricks that you use on a regular basis, share them in the comments section below.


PS: You may find this stackoverflow discussion useful for understanding different types of joins.


1 Comment

  1. Raghu

    One of the readers brought it to my attention that this query is showing duplicate column names for the same table. I figured out that the root cause of this problem is a missing join between syscat.tabconst and syscat.keycoluse.

    and a.tabschema = b.tabschema

    I added the above join and data is looking much better now.


Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.