Slow retrieval from freetext column

[Quoted] Dear all:

[Quoted] [Quoted] 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%') [Quoted] [Quoted] searches a column containing large blocks of text, and tries to match a [Quoted] string in that text-block.

[Quoted] [Quoted] I've been advised that indexing [using create index indexname...] on [Quoted] Oracle would be useless, since this would index each textblock as a left-justified single entity... rather than indexing each word/ phrase [Quoted] in the text-block.

[Quoted] I have 100,000 records in my table, (table-size is 200mb) and at 40,000 [Quoted] records the search took 12 seconds. At 100,000 records it takes 70 seconds.

[Quoted] [Quoted] I have dropped and recreated the table, this time making the initial [Quoted] 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 Received on Wed Jun 21 2000 - 00:00:00 CEST