Re: fragmentation and index maintainence

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 19 May 2004 18:25:24 +1000
Message-ID: <40ab19e6$0$31674$afc38c87@news.optusnet.com.au>

Jeremy wrote:

> In article <40ab0fd6$0$8987$afc38c87_at_news.optusnet.com.au>, Howard J.
> Rogers says...
>

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

>
>
> In fairness, I was dipping my toe in the water half expecting a 'yeah we
> know all about that' sort of response so wasn't expecting to proceed to
> substantiate my claims.

Then I apologise. Since Mile Ault's recent visit here, I have been in something of a 'pounce mode' where index maintenance is concerned (though readily admitting that Intermedia might well be a special case. It's not one I've spent any time looking at either, though I feel a bit of research coming on as we speak).

>

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

>
>
> Guilty as charged.

Well, that's refreshingly honest, at least!!

>

>>And that they can  quantify the boost. And, perhaps most importantly, that they can explain 
>>why it happens.

>
>
> Now that is where I have the problem - I cannot and am interested to
> know why and what one can do to minimise the problems.
>

>>

>
> Here is some precise information - from the following, you'll see a
> query time drop dramatically.
>
> 08:03:57 SQL> select count(candidate_id) from wd_cand_cvs where contains
> (cv_text,'oracle and (sap or peoplesoft)') >0;
>
> COUNT(CANDIDATE_ID)
> -------------------
> 20
>
> Elapsed: 00:00:01.07
> 08:04:13 SQL> drop index wd_cand_cvs_text;
>
> Index dropped.
>
> Elapsed: 00:00:05.00
> 08:05:19 SQL>
> 08:05:19 SQL> create index wd_cand_cvs_text on wd_cand_cvs
> 08:05:19 2 (cv_text) indextype is ctxsys.context;
>
> Index created.
>
> Elapsed: 00:15:02.05
> 08:20:21 SQL>
> 08:20:21 SQL>
> 08:20:21 SQL> select count(candidate_id) from wd_cand_cvs where contains
> (cv_text,'oracle and (sap or peoplesoft)') >0;
>
> COUNT(CANDIDATE_ID)
> -------------------
> 20
>
> Elapsed: 00:00:00.06
> 08:36:30 SQL>
>
>
> The table WD_CAND_CVS contains 11000 rows and is defined thus:
>
> 08:51:25 SQL> desc wd_cand_cvs
> Name Null? Type
> ----------------------------- -------- --------------------
> CANDIDATE_ID NOT NULL NUMBER
> CV_TEXT CLOB
> DATE_CREATED DATE
> USER_ID_CREATED NUMBER
> DATE_UPDATED DATE
> USER_ID_UPDATED NUMBER
>
>

Well, this is good because now its facts, though maybe a little off the beaten path for this particular thread. It certainly looks as if it could be a significant drop in execution time.

Maybe you could start a new thread with this? Just to be clear, when the query is taking 1+ seconds, how long has it been since the last drop-and-recreate? And how much DML has the table been subjected to since that last drop-and-recreate. Oh, and because I have a memory like a goldfish, what O/S and Oracle version is this again?

And finally, because I don't know so much about Intermedia as I should, perhaps someone else would like to comment, too, once those three clarifications are in place.

Regards
HJR Received on Wed May 19 2004 - 03:25:24 CDT