Re: HELP: select ... like speed issues

Hi,

    In the past when I have had to impliment an alpha search like this I have created a lookup table that splits each record into multiple single word records and then used this for the search.

    It will recquire some coding changes but will return sub-second response times. You would need a routine that takes each of the Varchar fields and splits it into individual words (I would recommend removing noise words like 'THE' and 'AND', store these in another table so you can easily add or remove words from the list) and then store them in the lookup table. On the search, you would again remove all of the noise words (If you code it right you can use the same routine) and if you then still have something to search with put it against the alpha search table to get the key to the actual records that match.

It is a lot of work to set up, but as I said earlier it is VERY fast to search.

Regards,

Rob Duke
Database Administrator
Motherwell Information Systems

Chris Markle wrote:

> "OnlineJobStore.com" wrote:
>
> > Hi everyone,
> >
> > Here's the deal:
> > - Oracle 8 running on NT box;
> > - there is a table that contains around 100,000 rows;
> > - each row contains a field (VARCHAR2) with 3000-4000 characters of
> > text;
> > - it is needed to run LIKE query against the field
> >
> > So far, the straightforward approach does not give satisfactory results
> > and does not scale well either. Any ideas about it? Please do not
> > mention "upgrade hardware" solution :)
> >
> > Thank you for help,
> > Victor Urvantsev
>
> check out the CON*TEXT cartridge...it is designed for this kind of
> situation.
Received on Tue Jun 15 1999 - 09:04:56 CDT