Date: Wed, 16 Oct 2002 08:04:09 -0800
Message-ID: <F001.004EA9F7.20021016080409@fatcity.com>
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:
Understanding Oracle Text concepts. Read the book!
Being able to change the SQL to use the OText special "CONTAINS" clause.
Understanding the data to be able to setup your thesaurus correctly.
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>).
Judge: "...and I'm no slouch myself."
Ty: "Don't sell yourself short, Judge. You're a tremendous slouch."
> From: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
> Sent: Wednesday, October 16, 2002 4:24 AM
> 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..
> --- "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?
> > EVENTID NOT NULL
> > NUMBER(10)
> > VER NOT NULL
> > QUEUETYPE NOT NULL
> > CHAR(16)
> > PUBLISHER NOT NULL
> > CREATETIME NOT NULL
> > LASTREADTIME
> > REMOVETIME
> > CONTENTS NOT NULL
> > VARCHAR2(4000)
> > The users do a query that looks like this:
> > SELECT EventId, QueueType, Publisher, CreateTime,
> > LastReadTime, RemoveTime,
> > Contents, Ver
> > 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
Received on Wed Oct 16 2002 - 11:04:09 CDT
Next message: ora ak: "RE: Any way to see the currently running SQL in one session."
Previous message: Vladimir Barac: "Drop snapshot & ORA-03113 - part 2"
Date: Wed, 16 Oct 2002 08:04:09 -0800
Message-ID: <F001.004EA9F7.20021016080409@fatcity.com>
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:
Understanding Oracle Text concepts. Read the book!
Being able to change the SQL to use the OText special "CONTAINS" clause.
Understanding the data to be able to setup your thesaurus correctly.
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>).
Judge: "...and I'm no slouch myself."
Ty: "Don't sell yourself short, Judge. You're a tremendous slouch."
> From: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
> Sent: 
Wednesday, October 16, 2002 4:24 AM
> 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..
> --- "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?
> > EVENTID NOT NULL
> > NUMBER(10)
> > VER NOT NULL
> > QUEUETYPE NOT NULL
> > CHAR(16)
> > PUBLISHER NOT NULL
> > CREATETIME NOT NULL
> > LASTREADTIME
> > REMOVETIME
> > CONTENTS NOT NULL
> > VARCHAR2(4000)
> > The users do a query that looks like this:
> > SELECT EventId, QueueType, Publisher, CreateTime,
> > LastReadTime, RemoveTime,
> > Contents, Ver
> > 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
Received on Wed Oct 16 2002 - 11:04:09 CDT
Next message: ora ak: "RE: Any way to see the currently running SQL in one session."
Previous message: Vladimir Barac: "Drop snapshot & ORA-03113 - part 2"