Find unnecessary Indexes

There's hardly a relational database without an index. It's essential if you have a lot of data and also want to find records there. However, just adding an index without thinking isn't smart either.

Imagine an index like a table of contents of a book. If the book contains one chapter, why do you need an overview? Right, it's not really helpful if you already know that there's just a single value.

Recipe #1 - Find unnecessary Indexes

The query below will analyze your indexes and list those that have a selectivity less than 20%. If you have a table with 100'000 rows and only 5'000 distinct values, you'll have a selectivity of 5% (5'000 / 100'000 = 0.05 → 5%).

The ideal selectivity is 100%, the lower it gets, the fewer distinct values you have. Unfortunately, there's not a fixed selectivity percentage which can be considered as bad in every case. It usually depends on the records you generally select. If you have a table with 100'000 rows and use an index to query for 90'000 rows, the index isn't really useful.

As a rule: If you query for less than 10% of the table's rows, you should quite likely add an index.

Of course, there are exceptions as always. If you just run that query once every century but have millions of write operations per day, it's also not really worth adding an index. Keep in mind that an index has to be updated if the records change.

Check this query to get a list of indexes which you should review and maybe remove:

SELECT di.owner, di.table_name, di.index_name,
       TO_CHAR((di.distinct_keys / di.num_rows) * 100, '999.99') selectivity,
       di.distinct_keys, di.num_rows, di.index_type
FROM dba_indexes di
WHERE di.num_rows > 0
  AND di.distinct_keys / di.num_rows < 0.2
ORDER BY di.distinct_keys / di.num_rows;