Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database – as with each write to the table, the index needs to be updated as well in the same transaction.
Perhaps, 5 years later, your database (and your queries) have evolved in a way for some indexes to no longer be needed. For instance, there are some obvious cases when two indexes are redundant:
-- Original design CREATE INDEX ON customer (first_name); -- 5 years later CREATE INDEX ON customer (first_name, last_name);
But in many other cases, things aren’t so obvious. For instance…
- you may have added an index on a foreign key, but as the table grew, your database started running more hash joins rather than nested loop joins, in case of which indexes are not used.
- Or, you’ve just entirely stopped querying for first names / last names.
- Or you’ve started using a predicate that is way more selective than actual names.
- Or your customers are suddenly all called Smith.

If your indexes are no longer used, you can (and should) delete them.
But how to find unused indexes
If you’re using Oracle database and you have access to the production system, there’s actually a very nice way to query the diagnostics tables in order to know whether there is any query in the cursor cache that is currently using your index. Just run:
SELECT sql_fulltext FROM v$sql WHERE sql_id IN ( SELECT sql_id FROM v$sql_plan WHERE (object_owner, object_name) = (('OWNER', 'IDX_CUSTOMER_FIRST_NAME')) ) ORDER BY sql_text;
What does this query do? It runs through all the SQL statements in the cursor cache (v$sql
) and for each one of them, checks if there is any execution plan element in the cursor cache (v$sql_plan
) which accesses the index. Done.
Of course, if this doesn’t return any results, it doesn’t mean that no one uses your index. There might still be a very rare query that happens only once a year, which gets purged from the cursor cache, otherwise.
But if you run this as a job over a while, you can already conclude that if this query doesn’t return any rows, your index is probably no longer needed.
Can I discover unneeded indexes?
Sure! Run a similar query that lists all the indexes that are not referenced from the v$sql_plan
table:
SELECT owner, index_name FROM all_indexes WHERE owner = 'OWNER' AND (owner, index_name) NOT IN ( SELECT object_owner, object_name FROM v$sql_plan WHERE object_owner IS NOT NULL AND object_name IS NOT NULL ) ORDER BY 1, 2
Again, this doesn’t say that your indexes will never be used, but they haven’t been used recently.
Now, I won’t actually show you the query that would use the above statement, run across its result in a PL/SQL loop and drop all the indexes using EXECUTE IMMEDIATE, because you might just actually do that to try it in production. But just in case you do want to try, here’s a hint :)
BEGIN FOR i IN (/* above query here */) LOOP EXECUTE IMMEDIATE 'DR0P INDEX "' || i.owner || '"."' || i.index_name || '"'; END LOOP; END; /
But as I said. Don’t actually do this!
Update: New Oracle 12cR2 feature: DBA_INDEX_USAGE
As you can see in the comments section, Oracle 12c now has this exciting new DBA_INDEX_USAGE
feature, which you can see here:
Oracle 12.2 has a feature for this called Index Monitoring. Check out this video from some of my colleagues, Connor and Chris. Watch from the beginning through 7:50 to see the details on just that feature: https://www.youtube.com/watch?v=oib5smLnA-g&index=5&list=WL
Oh wow, thank you very much for mentioning this! That is really useful! I’ll update the post