Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Know If a Given Index Can Be Dropped

DZone's Guide to

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
Free Resource

Check out the IT Market Clock report for recommendations on how to consolidate and replace legacy databases. Brought to you in partnership with MariaDB.

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.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

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