| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Full text search
Hi,=20
I think you can't avoid full table scan using the % as the first letter=
of your search keyword
Why don't you use a table like this (let's call it rowid_tab):
Keyword Row_id ---------------------- ------------------- SearchKey01 client_note_rowid01_01 =20 SearchKey01 client_note_rowid01_02 SearchKey02 client_note_rowid02_02
SELECT text_value=20
FROM client_note=20
WHERE rowid in (select Row_id from rowid_tab where keyword =3D'SPEED')
In this way you will be able to speed up the query using th most used sea= rch keywords.
I'm sorry for my english an I hope this helps Davide=20
-----Original Message-----
From: Witold Iwaniec wiwaniec_at_novalistech.com
Sent: Wed, 13 Sep 2000 04:45:30 -0800
To: ORACLE-L_at_fatcity.com
Subject: Full text search
Hi all
In our product we have to support long text fields so we use=20
columns defined as VARCHAR2 (2000)
It seems to do the job but we are looking for a way to enable full=20
text search.=20
Running statements like:
'SELECT text_value FROM client_note WHERE text_value LIKE=20
'%SPEED%'=20
would be very slow and always require full table scan. Is there a=20
better way of doing it, especially in the Oracle 8i world?
Thanks
Witold
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Witold Iwaniec
Senior Software Developer
NovaLIS Technologies
wiwaniec_at_novalistech.com =20
http://www.novalistech.com
--=20
Author: Witold Iwaniec
INET: wiwaniec_at_novalistech.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).
_________________________________________________________________________=__
 |
 |