Re: Search of open text field.

On 18 Jan 2002 08:33:12 -0800, vdolt
<vadolt_at_yahoo.com> a écrit:
>I am running Oracle 8.1.6 on NT. There is a table with a "description"
>field
>which is varchar2(4000). People will have an interface to search on
>that field.
>There will be a lot of records (2-3 million) and field is large, so I
>can't possibly create an index. What is the best way to do that? I
>have heard about interMedia, would that help me to do that? Is it
>separately licensed feature?

You indeed have an interest in using interMedia Text. Beware, on 8i, interMedia Text is still a little annoying (much better in 9i).

You've got to start creating the ctxsys user and role (scripts in $ORACLE_HOME/ctx) and I'm afraid you'll have to read the doc, 'cause you can do quite a number of things and so there is no real out of the box option.

Just a few thing :
- always create/drop conText indexes whith the ctxsys user. Never do it as dba or you'll have to clean up the mess by hand. - you have first to create a "lexer". The lexer is the configuration that will be used to create your index. You specify your "preferences" (stopword list, use stemming or not, the storage clause of your index, if you want to cast accentuated characters to their base letter, and so on). - then you create your index, using you lexer.

And then , you can query your index, and the answer coming back pretty fast :-)

Ho, I've just seen the "I can't create an index". I'm afraid you'll have to. I you do not, your only choice is "like '%my_word%' and you'd better disable the field.

interMedia _does_ create indexes, and they tend to be quite big ...

-- 
Damien