RE: Can I index this query?

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