Re: Slow retrieval from freetext column

One option to look into is the keyword indexing of OMNIDEX, which can layer on top of Oracle and perform %string% searches instantly, no matter what size the table. See http://www.disc.com/keyword.html and http://www.disc.com/artindex.html or contact me for more information.

Cheryl Grandy
DISC
303 444-4000
cgrandy_at_disc.com

In article <39505F88.71AB4C83_at_lib.monash.edu.au>,   kate.roberts_at_lib.monash.edu.au wrote:
> Dear all:
>
> My problem resides with the slowness in retrieving results when
> searching on one column of one table.
>
> The query ( select * from tablename where columnname like '%string%')
> searches a column containing large blocks of text, and tries to match
 a
> string in that text-block.
>
> I've been advised that indexing [using create index indexname...] on
> Oracle would be useless, since this would index each textblock as a
> left-justified single entity... rather than indexing each word/ phrase
> in the text-block.
>
> I have 100,000 records in my table, (table-size is 200mb) and at
 40,000
> records the search took 12 seconds. At 100,000 records it takes 70
> seconds.
>
> I have dropped and recreated the table, this time making the initial
> extent more than 200mb. Still no improvement.
>
> Does anyone know of a way to create an index on words/ phrases in
> textblocks- in a "text" column??????
>
> Or can anyone suggest another way to hasten the retrieval process????
>
> Regards,
>
> Kate Roberts
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.