Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Intermedia Text - Performance degradation over time
It's quite a long time since I played with Context, Oracle Text, Intermedia -- whatever it is, was, or will be called. But the important thing to remember is that a user defined indextype can do almost anything in the database, and context indexes are just one case of user defined index types.
From memory, when you create a context index, Oracle creates a handful of tables and indexes behind the scenes, including IOTs and LOBs. Indexing activity on both these types of objects can easily fall outside the 'standard' behaviour pattern of B-trees.
I have a memory of two distinct problems: first was that the addition of a new document could cause new entries to be created in some tables/indexes when the ideal solution would have been to extend an existing entry. second (exactly the opposite): some updates would grow existing entries by appending to them - but the nature of the code would leave several earlier versions of the item behind.
To discover more, clone the base table into
an empty schema, create the context index,
and see how many other objects spring into
existence in the schema. (In the production
schema you could do a before/after query
on user_objects to see objects disappear
and appear on the rebuild). This would
allow you to generate stats on the objects
to see if any specific objects were doing
strange things with their space usage and
costing you time as a consequence.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st "Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message news:MPG.1b152dc4dd1b4c85989bfa_at_news.individual.net...Received on Wed May 19 2004 - 05:33:17 CDT
> Per HJR's request, starting a new thread on this.
>
> Oracle 8.1.7.4 on Solaris 2.6
>
> We run ctxsrv in the background to automatically update the IM text
> indexes as documents are added/updated/removed. Rebuild perhaps every
> couple of weeks during which time perhaps 400 reords might have been
> inserted/updated.
>
> Rebuilding the text index (by dropping and recreating the index) yields
> a massive performance gain. Is this normal?
>
> Table is defined:
> 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
>
> and contains approx 11,000 records
>
> The following shows the performance gains before & after recreating the
> index:
>
> 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>
>
>
>
>
> --
>
> jeremy
>