-- Original design
CREATE INDEX ON customer (first_name);
-- 5 years later
CREATE INDEX ON customer (first_name, last_name);
- 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.

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;
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 thev$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
BEGIN
FOR i IN (/* above query here */) LOOP
EXECUTE IMMEDIATE
'DR0P INDEX "' || i.owner || '"."' || i.index_name || '"';
END LOOP;
END;
/
Update: New Oracle 12cR2 feature: DBA_INDEX_USAGE
As you can see in the comments section, Oracle 12c now has this exciting newDBA_INDEX_USAGE
feature, which you can see here:
Thanks, Dan McGhan for mentioning this!
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