There is also the issue of keeping an index that is not used in any explain plan, but is required to prevent a foreign key locking problem.
hth connor --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > Mark, > > The obvious redundant indexes are the ones the n > columns of which happen to be, and in the same > order, the nth first columns of another index. There > is not much which can be said besides. First of all, > I would question your definition of "redundant" as > "never used by Oracle". Some indexes are sometimes > used which in truth should not if you care a bit > about performance; being used by Oracle is not a > guarantee that they speed up queries. If I were you, > I would try first to narrow the scope. <well-known > tune>Disk space costs nothing these days</well-known > tune>. I do not fully agree, especially as backing > up your Terabytes has a cost, in time if nothing > else. But let's put this aside. Where your indices > hurt, it's quite obviously with DML (for updates, > only if the updated columns are indexed), first > because you have of course additional memory > scanning/writing and I/Os, and second because since > indices are by nature more compact than tables, they > are more susceptible of content! > ion, with all transactions fighthing over the same > small amount of bytes (<ladies please skip>the > coming January sales could be a good image</ladies > please skip>). If you concentrate on those of your > tables which are most heavily inserted and deleted > and try to get a good picture of the queries against > them, I believe that you will probably address 90% > of issues. > > HTH, > > SF > > >----- Original Message ----- > >From: "Mark Richard" <[EMAIL PROTECTED]> > >To: Multiple recipients of list ORACLE-L > ><[EMAIL PROTECTED]> > >Sent: Sun, 22 Dec 2002 20:53:36 > > > >Dear List, > > > >Firstly - Merry Christmas to those who have not > >already departed for the > >holiday season. > > > >I'm currently doing some investigation based around > >indexes and would like > >everyone's opinion: What is everyone's preferred > >approach to identify > >redundant (as in "never used by Oracle") indexes? > >I believe Oracle 9 might > >have a feature to set a flag on objects and then > >check back later to see if > >they have been accessed however we're still stuck > >on 8.1.7.4 (Solaris). > >Some of my thoughts include: > > > >* Can query for physical disk i/o at a tablespace > >level easily, however > >more difficult to go to an object level. > > > >* Could create a trace file and then inspect > >explain plans for existence of > >index accesses, however trace file probably not > >practical to capture for a > >long period of time. > > > >* Ideal statistic would be something along the > >lines of "index x used y > >times in last 24 hours", however a simple "index x > >was used in the last 24 > >hours" would be ok. > > > >Obviously we are searching for indexes to remove > >and identifying those > >which aren't queried over a set period of time > >would be good candidates for > >a starting point. Any advice you might have would > >be greatly appreciated. > > > >Regards, > > Mark. > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Stephane Faroul > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
