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).
