Finding duplicate indexes

Over time database schemata are subject to changes such as index additions and deletions. It is not uncommon to end up with multiple indexes that are equivalent in terms of query execution but might be defined with different names or even different columns.

This duplication of indexes has negative consequences for your database:

• Increased size: The more the indexes, the bigger the database.
• Lower performance: Each index has to be updated on modifications of the respective table, wasting precious I/O and CPU resources.
• Increased schema complexity: Schema maintenance and understanding of the tables and relationships gets more complicated.

For those reasons, you should be concerned about superfluous indexes.

In this recipe, we will present a way to quickly find out which indexes can be dropped from a table as they are functionally equivalent (if not necessarily formally identical) to another one.

Getting ready

In order to run the program presented here, you will need a Java...