RE: Can I index this query? slightly OT-just InterMedia Text option

Jesse,
since you o have such a good experience, could you please comment on the following: I have installed InterMedia Text (8.1.7 on Win2K sp.2) and 400MB has grown to 4.5 GB. There are 4 required indices, two for each table - on English and French version of varchar2(2000). Number of data base objects (user + ctxsys schemas) is 4 times larger. Search is perfect, inserts are using 100% CPU and killing the server.

What have I done wrong? Do you have any idea? By the way, I have read TFM.

TIA
inka

-----Original Message-----
Sent: Wednesday, October 16, 2002 12:04 PM To: Multiple recipients of list ORACLE-L

I don't think it's really that big of a step. Until a rewrite of our 3rd-party inventory system (i.e. we didn't write the damn thing), we have to rely on part description stored in two separate tables (don't ask!) in order to search for parts.

By installing and adding a Context/Intermedia/Text/whateverthehell index, we've gone from a two-table FTS (200K total rows on moderately wide tables) to sub-second index access. The difficulties in getting to this point are:

1. Understanding Oracle Text concepts. Read the book!
2. Being able to change the SQL to use the OText special "CONTAINS" clause.
3. Understanding the data to be able to setup your thesaurus correctly.
4. Determining if/when to rebuild the index.

It took me about a week of research and testing before installing and using it (of course that week's work was spread over three weeks!), and a day or two of tweaks afterwards. In my case, I needed to also create some replication-like triggers and tables to combine data from our two tables into a single, indexable, searchable table.

Fire it up! We've been pretty happy with the results (except when a user wants to return every row with a "Z" in the string...<sigh>).

GL! :)

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

Judge: "...and I'm no slouch myself."
Ty: "Don't sell yourself short, Judge. You're a tremendous slouch."

> -----Original Message-----
> From: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
> Sent: Wednesday, October 16, 2002 4:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Can I index this query?
>
>
> You could always consider something like OracleText to
> assist with these kind of searches, but thats a big
> step..
>
> hth
> connor
>
> --- "Carle, William T (Bill), ALCAS" <wcarle_at_att.com>
> wrote: > Howdy,
> >
> > I have a table that has almost 2 million rows
> > called eventqueueentry. The layout looks like this:
> >
> > Name Null?
> > Type
> > ----------------------------------------- --------
> > ----------------------------
> > EVENTID NOT NULL
> > NUMBER(10)
> > VER NOT NULL
> > NUMBER(10)
> > QUEUETYPE NOT NULL
> > CHAR(16)
> > PUBLISHER NOT NULL
> > CHAR(16)
> > CREATETIME NOT NULL
> > DATE
> > LASTREADTIME
> > DATE
> > REMOVETIME
> > DATE
> > CONTENTS NOT NULL
> > VARCHAR2(4000)
> >
> > The users do a query that looks like this:
> >
> > SELECT EventId, QueueType, Publisher, CreateTime,
> > LastReadTime, RemoveTime,
> > Contents, Ver
> > from
> > EventQueueEntry where QueueType = 'CodeUpdate' AND
> > Contents LIKE
> > '%TrackingEventId=27668677%' ORDER BY EventId
> >
> > The queuetype field has only 3 different values. The
> > value in the contents field is close to being unique
> > (high cardinality) but, as you can see, they are
> > picking off a value somewhere in the middle of a
> > varchar2(4000) field. Understandably, their query is
> > slow. Is there anything I can do with an index to
> > speed this up?
> >
> >
> > Bill Carle

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Inka Bezdziecka
  INET: IBezdziecka_at_cupe.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).