Re: fragmentation and index maintainence

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 19 May 2004 17:42:29 +1000
Message-ID: <40ab0fd6$0$8987$afc38c87@news.optusnet.com.au>

Jeremy wrote:
> In article <40aa63a9$0$8987$afc38c87_at_news.optusnet.com.au>, Howard J.
> Rogers says...
>

>>Jeremy wrote:
>>
>>
>>>Intermedia Text indexes need regular rebuilds otherwise the query 
>>>performance can be severely degraded.
>>>
>>
>>In the first place, I can't see any reference to intermedia in the OP's 
>>question. 

>
>
> I threw it in because the OP was referring to fragmentation of indexes
> and the 'rules of thumb' that people may use to mange their rebuild. As
> intermedia text indexes are 'indexes' I thought I'd contribute my
> experience.
>
>

>>And secondly, where's the statistics/test case to back that 
>>statement up?
>>

>
>
> Practical experience with 8.1.7.4
>
> We run ctxsrv in the background to automatically update the indexes as
> documents are added/updated/removed.
>
> Rebuilding the text index (by dropping and recreating the index) yields
> a massive performance gain. Whether this is attributable to poor
> design/implementation of the application I couldn't say, but the
> scenario holds true.

Uh huh. So we don't know whether this is actually an "index fragmentation" thing or an intermedia index becoming stale thing. And there's no figures for performance before or after. And it's all just anecdote.

And it doesn't appear even to be a rule of thumb guide to rebuilding indexes, because you're actually dropping and re-creating them, which is a rather different matter.

Precision and science just went out the window again, I guess.

I'm not having a go at you particularly, Jeremy. Just wishing that when someone claims performance boosts from an index rebuild they actually know the difference between a rebuild and a re-create. And that they can quantify the boost. And, perhaps most importantly, that they can explain why it happens.

Regards
HJR Received on Wed May 19 2004 - 02:42:29 CDT