Re: fragmentation and index maintainence

Howard J. Rogers wrote:
>
> 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
'ctxsrv' is a recipe for disaster for text indexes (which is why from 816 onwards Oracle strongly recommend you don't use it). We moved from ctxsrv to a 5 min 'sync' call and the index growth (which typically maps to response time for text indexes) slows dramatically. Combined with the occasional 'optimize' then the index build requirement often drops away.

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------