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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED]] > 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
