When you happen across a table with lots of indexes, what’s the first thought that crosses your mind? 

Okay, having gotten that bit of venting out of the way, what’s the second thought?  Are all of these indexes actually used?  That’s a legitimate question and one that comes up from time to time in our remote Oracle DBA practice.  Unneeded indexes not only waste space but also introduce unnecessary overhead to table updates.  So let’s talk about what can and should be done about it.

Index usage monitoring is a fairly common subject of chatter in the various Oracle-related discussion boards and blogs.  There are scripts available, some free and some for a price, which boast the ability to identify unused indexes easily enough and allow you to just drop them, thereby releasing tons of space and cutting update overhead dramatically.  Our take on the matter – not so fast!

Determining whether an index is a necessary part of the database is not as easy as turning on monitoring and running a few scripts.  Sure, turning on monitoring is easily done with the ALTER INDEX statement and then checking the V$OBJECT_USAGE view (as the index owner) will tell you whether the index has been accessed since monitoring was turned on.  But there are a couple of gotchas with that approach:

• In versions of 10g prior to 10.2.0.5 and 11gR1 gathering stats on a table with the CASCADE option set will mark an index as “used” in the V$OBJECT_USAGE table if monitoring is on.  See Bug # 430034.1 in Metalink.
• Indexes which support foreign keys are not flagged as used when a relevant update occurs even though the index was indeed used behind the scenes to avoid a table lock.

Thus indexes might be flagged as used even though they are not necessary and may not be flagged when they are indeed a vital part of the database.

Some alternatives exist to the V$OBJECT_USAGE view, such as querying AWR or Statspack data to determine whether an index was part of an execution plan.  But this approach has a separate downfall in that an index might have been incorrectly used by the optimizer.  Perhaps the index was used but is its existence justified?  Is it really necessary?  You might end up dropping an index that should have been used in favor of one that should not have been.

So what’s the point?  The point is that sometimes there is no good shortcut for diligence.  Let us keep the cart behind the horses and talk to the data modelers and application developers about whether indexes are necessary.  It is easy enough to identify redundant indexes such as those which are subsets of others on the same table so we could certainly make recommendations, but throwing the application into a tizzy by dropping indexes we declared useless through the running of our scripts is a good way to insure taking up residence in the proverbial dog house.