Over a million developers have joined DZone.

How to Know If a Given Index Can Be Dropped

When using Oracle, here's a trick to keep in mind to help you determine whether you can get rid of an index hanging over your head.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove.

Antoine de Saint Exupéry in Terre des Hommes

As SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a new index might solve the problem. But it also creates new problems. The more indexes you have, the slower your insertions and updates will become, and obviously, the more disk space your data will use.

But how do we know if we can remove an existing index? How do we know if there won't be any performance regression?

In Oracle, there's a way to answer this question! Just look at the cursor cache of your production system. In a previous blog post about UNIQUE constraints, we have added an index called "I1DATA" to our database, and we've run some queries that used this index.

Several days later, we want to know if that index is still being used in our "production" database. Just run the following query:


SELECT *
FROM v$sql
JOIN v$sql_plan USING (sql_id)
WHERE object_name = 'I1DATA'


This query uses Oracle's system views, which reveal tons of information about your production system. The above query returns something like the following data:


SQL_TEXT                             LAST_ACTIVE
------------------------------------------------
SELECT COUNT(*) FROM X1 JOIN Y1  ... 14.07.16
SELECT count(*) FROM x1 JOIN y1  ... 14.07.16
SELECT count(*) FROM x1 WHERE a  ... 14.07.16
SELECT COUNT(*) FROM X1 WHERE A  ... 14.07.16


A whole lot of additional information about the actual execution plan is contained in the result set, e.g. whether the index was used for an INDEX RANGE SCAN, or something else. The interesting thing at this point is:

Yes, the index is still being used by several queries in the cursor cache

… so we probably can't delete it yet.

Caution

These views won't reliably tell you if an index is used. They are using the cursor cache, which may be configured in various ways, including not caching cursors for too long, or not caching all cursors.

In any case, the following rules can be established:

  • If an index appears in the cursor cache several times, you shouldn't remove it.
  • If an index appears in the cursor cache only once, perhaps it isn't really needed for that particular query. Analyze.
  • If an index does not appear in the cursor cache, run the query again, frequently, and after some time (and if you're sure), you can remove it.

Caveats

  • Some indexes may have been created for reports that run very infrequently. The suggested approach won't cover that scenario.
  • This information is most reliable from the production system. You cannot possibly obtain any meaningful information from your developer box.

Remember: This is just a nice trick, not a reliable rule. But it certainly does help you assess whether that big, expensive index that doesn't appear to be useful can be safely removed.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:
index ,database performance

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}