CODEWITHSUNDEEP
sqloracle-databaseoracle11g
otocon
otocon

Reputation: 1049

Oracle -- unexpected CONTAINS results

DB Info:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE   11.2.0.3.0  Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Setup:

CREATE TABLE my_contains_test(    
   USERID VARCHAR2(8) NOT NULL,
   SEARCH VARCHAR2(40),
   CONSTRAINT contains_test_pk PRIMARY KEY (USERID)
);

INSERT ALL
INTO my_contains_test VALUES ('HUNTERW','Willie Hunter')
INTO my_contains_test VALUES ('HUWU','Will Hu')
SELECT * FROM dual;

create index ind_contains_test on my_contains_test(search) indextype is ctxsys.context;

Query:

select m.*, contains(search, 'will% and hu%', 1) as c_result from my_contains_test m;

Results:

    USERID   SEARCH         C_RESULT
    HUNTERW  Willie Hunter  4
    HUWU     Will Hu        0

Is that a good result for second record (C_RESULT == 0)? I can't figure out what is going on.

Here goes the good part. Change names to different one, like Willie to Billie and Will to Bill, query to:

select m.*, contains(search, 'bill% and hu%', 1) as c_result from my_contains_test m;

and result is correct:

USERID  SEARCH          C_RESULT
HUNTERW Billie Hunter   4
HUWU    Bill Hu         4

So change in one position makes it work differently. I have no clue what's that all about. Any ideas how to solve it would be great.

Upvotes: 1

Views: 160

Answers (1)

krokodilko
krokodilko

Reputation: 36107

A word will is on a default stop list for english language, and it is not indexed by default.
See this link: http://docs.oracle.com/cd/B28359_01/text.111/b28304/astopsup.htm#CEGBGCDF

Create your own stop-list and use it in the index, try this code (you must have granted an execute privilege on ctx_dll):

drop index ind_contains_test;
exec CTX_DDL.CREATE_STOPLIST('my_empty_stoplist','BASIC_STOPLIST');
create index ind_contains_test on my_contains_test(search) 
indextype is ctxsys.context parameters('stoplist my_empty_stoplist');

select m.*, contains(search, 'will% and hu%', 1) as c_result 
from my_contains_test m;

USERID   SEARCH                                     C_RESULT
-------- ---------------------------------------- ----------
HUNTERW  Willie Hunter                                     4 
HUWU     Will Hu                                           4

Upvotes: 1

Related Questions