CODEWITHSUNDEEP
oracle-databasefull-text-search
Yangrui
Yangrui

Reputation: 1247

Oracle Text Search doesn't work on some words

I am using Oracle' Text Search for my project. I created a ctxsys.context index on my column and inserted one entry "Would you like some wine???". I executed the query 

select guid, text, score(10) from triplet where contains (text, 'Would', 10) > 0

it gave me no results. Querying 'you' and 'some' also return zero results. Only 'like' and 'wine' matches the record. Does Oracle consider you, would, some as stop words?? How can I let Oracle match these words? Thank you.

Upvotes: 2

Views: 3655

Answers (2)

ajmalmhd04
ajmalmhd04

Reputation: 2602

so, i found that the query's output is perfect according to the stop word lists that is in the oracle.

those words can be found in the ctxsys package, and you could query for the stoplist and the stop words using 

SELECT * FROM CTX_STOPLISTS;
SELECT * FROM ctx_stopwords;

and yes, the oracle consider 'you', 'would' in your query as stop words. The following lists are the default stop words.

a   did     in  only    then    where
all     do  into    onto    there   whether
almost  does    is  or  therefore   which
also    either  it  our     these   while
although    for     its     ours    they    who
an  from    just    s   this    whose
and     had     ll  shall   those   why
any     has     me  she     though  will
are     have    might   should  through     with
as  having  Mr  since   thus    would
at  he  Mrs     so  to  yet
be  her     Ms  some    too     you
because     here    my  still   until   your
been    hers    no  such    ve  yours
both    him     non     t   very     
but     his     nor     than    was      
by  how     not     that    we   
can     however     of  the     were     
could   i   on  their   what     
d   if  one     them    when

if you need to remove some specified words (or add stop words),

(you need **GRANT EXECUTE ON CTXSYS.CTX_DDL to you **) then, you've to execute a procedure, example:

begin
ctx_ddl.remove_stopword('mystop_list','some');
ctx_ddl.remove_stopword('mystop_list','you');
end;

refer link for various functions in ctx_ddl package

you could get full description about the created ctx index by querying,

select ctx_report.describe_index('yourindex_name') from dual;

Upvotes: 4

knagaev
knagaev

Reputation: 2967

Look at the docs

In paragraph "4.1.5 Querying Stopwords" you can get some useful info :)

Upvotes: 0

Related Questions