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 <sfaroult_at_oriolecorp.com> 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" <mrichard_at_transurban.com.au>
> >To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> >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: sfaroult_at_oriolecorp.com
>
> 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: ListGuru_at_fatcity.com (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: hamcdc_at_yahoo.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Mon Dec 23 2002 - 03:23:48 CST