Over a million developers have joined DZone.

Does Your Database Really Use Your Index?

DZone's Guide to

Does Your Database Really Use Your Index?

As databases grow and indices are added, we sometimes forget just how many we've added and if they're actually used. Write performance matters, after all.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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, five 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.
Everyone is called Smith - Tough for Indexing

Everyone is called Smith — tough luck for indexing!

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)
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

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

  FOR i IN (/* above query here */) LOOP
     'DR0P INDEX "' || i.owner || '"."' || i.index_name || '"';

But as I said. Don’t actually do this!

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

database ,index ,queries ,performance

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}