Re: Seeking a substring indexing package (or something)

On 31 Jul 1997 20:53:01 GMT, kevin_at_cpm.com (Kevin Bourrillion) wrote:

>I have a few tables with LONG and/or VARCHAR2(1000+) columns where large
>quantities of text are stored. People need to search this information
>using queries of the form:
>
>SELECT * FROM table
>WHERE (column LIKE '%string1%'
> OR column LIKE '%string2%')
> AND column LIKE '%string3%')
>
>etc.
>
>Is there any way that I can build a "substring index" or "keyword index"
>on this column that will speed up these queries? Something similar to
>what wais does, if I'm not mistaken

Kevin, You did not say what OS/platform you're on, but if you're lucky enough to be running under UNIX and your application does not require that it be done within the database you might consider "filtering" the table data using one of the many excellent UNIX text processing utilities that make this kind of thing a snap (awk, for example). It all depends on a lot of factors, but depending on the situation, I've often been able to get much better performance by doing the basic select within Oracle and writing the data out to a UNIX process (via a file or pipe) which filters out what I need. Sometimes I can use the results directly, sometimes I write the keys back into the DB in a temp table and then use this to return the data I need and so on.

I don't know if this is feasible for you, of course.

George Received on Sat Aug 02 1997 - 00:00:00 CDT