The LASTUSED column in the SYSINDEXSPACESTATS table contains a date indicating the last time the index was used. Any time the index is used to satisfy a SELECT, FETCH, searched UPDATE, searched DELETE, or to enforce a referential constraint, the date is updated.
This helps to solve the problem of determining whether or not an index is being used. Standard operating advice is to DROP or delete anything that is not going to be used. But trying to determine whether something is actually used or not can be difficult at times.
You could always query your PLAN_TABLEs or the plan and package dependency tables in the DB2 Catalog for static SQL. But what about dynamic SQL? That is more difficult. Now, as of DB2 V9, you can simply query the LASTUSED column to see when the index was last used. The LASTUSED date is by partition. So, for a partitioned index, the last used date of each partition in the index should be checked.
Of course, you will have to give it some time because you might have an index supporting a rarely used query. Most shops have queries and programs that run quarterly, or even annually, but nevertheless are very important... and you wouldn't want to drop indexes on those queries even though they do not run frequently because when they do run, they are important...
Examine the LASTUSED column over time to determine which indexes are truly not being used, and then DROP the unused indexes.
In the case of an index that is only used annually, would it make sense to create the index immediately before it is used and then drop it afterwards?
ReplyDeleteAlso, don't forget that the index may be there to enforce uniqueness.
ReplyDeleteMark, for annual use only, it could possibly make sense to build the index right before it is needed and drop it thereafter. If the table on which the index is built is modified frequently, this course of action makes even more sense. If the table is not modified often and the index is not overly large, though, you might not want to go to the bother. Also, it is easy to forget to build the index, so having it around all the time removes that as an obstacle.
ReplyDeleteAnd yes, Larry, good point! Let's NOT forget indexes used to enforce uniqueness.
ReplyDelete