Intermedia Text - Performance degradation over time

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Wed, 19 May 2004 09:40:21 +0100
Message-ID: <MPG.1b152dc4dd1b4c85989bfa@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 - 03:40:21 CDT