Re: fragmentation and index maintainence

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Wed, 19 May 2004 08:56:00 +0100
Message-ID: <MPG.1b152360ab9268cb989bf7@news.individual.net>

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.  

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

> 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

-- 

jeremy

Received on Wed May 19 2004 - 02:56:00 CDT