Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Intermedia Text - Performance degradation over time

Re: Intermedia Text - Performance degradation over time

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 19 May 2004 10:33:17 +0000 (UTC)
Message-ID: <c8fd5d$rj9$1@sparta.btinternet.com>

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

> 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
>
Received on Wed May 19 2004 - 05:33:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US