Re: Q: Identify indexes which are no longer used ?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sat, 03 Jul 1999 18:05:56 +0800
Message-ID: <377DE084.2F3E@yahoo.com>

Jonathan Lewis wrote:
>
> That's given me a cute idea for abusing the system:
>
> If you have Oracle 8, and aren't yet using the 3 buffer pools,
> allocate some space to the keep pool, and allocate a suspect
> index to the keep pool.
>
> If you then do
> select set_ds,count(*) from x$bh group by set_ds
> at the end of each day, you can see if ANY blocks
> get into the keep pool - if so they have to be your index,
> if not then your index is not being used.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Jurij Modic wrote in message <37819916.15989712_at_news.siol.net>...
> >On Thu, 01 Jul 1999 21:05:43 GMT, tedchyn_at_yahoo.com wrote:
> >
> >>Sir, is there a way identifying obsolete indexes which are no longer
> >>used ?
> >
> >Beside the method suggested by Jonathan Lewis, another way to find
> >this would be to periodically check the SYS.V$BH view for any block of
> >the suspected index being in the buffer cache. Something like:
> >

There is also the time honoured method of dropping all of the indexes and telling the user not to have complained in the first place..

Just kidding..

hee hee
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Sat Jul 03 1999 - 05:05:56 CDT