But in many other cases, things aren’t so obvious. For instance…
-- 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 indexesIf 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:
What does this query do? It runs through all the SQL statements in the cursor cache (
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 the
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 :)
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
But as I said. Don’t actually do this!
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_USAGEAs you can see in the comments section, Oracle 12c now has this exciting new
DBA_INDEX_USAGEfeature, which you can see here:
Thanks, Dan McGhan for mentioning this!