Re: fragmentation and index maintainence

Jeremy wrote:
>
> In article <40AB5BF9.4E29_at_yahoo.com>, Connor McDonald says...
> > '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.
> >
>
> Oh I didn't realise that ctxsrv wasn't the approved way of doing this.
> There used to be a ctxsrv executable that had to be running all the time
> in order to even yun a text query (I think that was back in the ConText
> days).
>
> So you use DBMS_JOB to schedule periodic (you say mins) 'sync'
> instructions - what does this call look like?
>
> And as a result of making this change index growth is then proportional
> to the increase in data stored (as you would expect it to be!).
>
> cheers
>
> --
>
> jeremy

(To answers your final question) its closer...

If you're doing lots of deletes, then it tends to take some significant optimize calls (or a full rebuild) to get that space back, but other than that, we've been pretty happy with just regular (ie 5min) syncs

The call is just: ctx_ddl.sync_index('index_name')

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

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