When unused – isn’t.
So we recently reviewed a table with no fewer than 23 indexes on it. A combination of alter index… monitoring usage and reviewing DBMS_HIST_SQL_PLAN for plans that referenced the indexes found 8 indexes that were never used. As this table is also heavily updated we removed the indexes identified as unused. This was, fortunately, a QA system under change control. See if you can spot when the change was made.
Read here –
http://dfitzjarrell.wordpress.com/2008/04/17/to-b-or-not-to-b/
I said as much toward the bottom of the post.
David Fitzjarrell
7 Mar 13 at 4:28 pm
Thanks for dropping by David. This *was* a test system. Unfortunately v$object_usage is a bit unreliable, but the real mistake here was not remembering that DBA_HIST_SQL_PLAN records the plan for child cursors found in AWR so if your index makes the query really efficient, it probably won’t be captured in the workload repository until you drop the index when suddenly its much more likely to be a Top N SQL.
Niall Litchfield
7 Mar 13 at 4:37 pm
So did you drop them or just make them invisible?
Dominic Brooks
7 Mar 13 at 4:44 pm
Which would be more sensible
. No we dropped them because of the concern about the maintenance overhead of the ‘unused’ indexes. That overhead remains for invisible indexes. Clearly a better approach is
set invisible
wait ‘n’ hours
drop.
Setting ‘n’ is problematic though.
Niall Litchfield
7 Mar 13 at 4:52 pm
Will you please share the type of indexes, i.e. FK, non-unique?
Thanks Michael.
mdinh
7 Mar 13 at 5:22 pm