"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3f74c5c5$0$15134$afc38c87_at_news.optusnet.com.au>...
> Ryan Gaffuri wrote:
>
> > Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
> > news:<3F7411E0.4B2B_at_yahoo.com>...
> >> Mark wrote:
> >> >
> >> > Hi all dba users,
> >> > I'd like to know if there's some good sql script / indicator to detect
> >> > if an index need to be rebuilt.
> >> > Thanks a lot
> >> > Mark
> >>
> >> Don't.
> >>
> >> Or use coalesce
> >>
> >> hth
> >> connor
> >
> > isnt there something to look for in index_stats? I have found that if
> > we do alot of deletes and rebuild it there is a big speed increase(yes
> > I know creating the table or doing a 'move' is import too).
>
> Try reading other posts in the thread. I answered you about index_stats.
>
> But when Connor tells you 'don't', that's something worth listening to.
>
> Regards
> HJR
Anurag posted a rather lengthy message that showed a case where the
index should be rebuilt rather than coalesced. The rebuild took 1
minute, whereas teh coalesce took 40 minutes. The coalesce won't
entirely defragment the index in some severe circumstances. In this
case, the question that needs to be asked is whether you have the
right type of index, or whether it is configured properly. If you
rebuild a 400MB index and it is 10MB when you're done, you need to
start looking for something that will address the root cause.
Received on Sat Sep 27 2003 - 20:55:16 CDT
