Oracle Contains is not working

Question

I have a column in my table that is context indexed.

CREATE INDEX CIDX_MUSTFIXBY ON TABLE
  (MUST_FIX_BY)
INDEXTYPE IS CTXSYS.CONTEXT
NOPARALLEL;

And i am trying a query with where condition

AND must_fix_by LIKE 'Q2%'

and it returns rows.

However when i try query with where condition

AND contains(must_fix_by, 'Q2') > 0

and it does not return any rows.

Can somebody please tell me why like is working and contains is not?

Answer 1

Two possible reasons - the index may not be synchronized, and CONTAINS seems to match words while LIKE matches strings.

An example of two strings, where LIKE matches both, but CONTAINS matches neither:

create table test1(must_fix_by varchar2(4000));
create index cidx_mustfixby on test1(must_fix_by) indextype is ctxsys.context;
insert into test1 values('Q234567');
insert into test1 values('Q2 234567');
select * from test1 where must_fix_by like 'Q2%';

MUST_FIX_BY
-----------
Q234567
Q2 234567

select * from test1 where contains(must_fix_by, 'Q2') > 0;

no rows selected

By default, CONTEXT indexes need to be manually synchronized. You either need to run: exec ctx_ddl.sync_index('cidx_mustfixby');, or you need to create you index with on commit.

exec ctx_ddl.sync_index('cidx_mustfixby');
select * from test1 where contains(must_fix_by, 'Q2') > 0;

MUST_FIX_BY
-----------
Q2 234567

This fixes one of the issues. But Q234567 is still not matched. I don't know a lot about Oracle Text, and I can't even find a simple description of how CONTAINS works. But it seems to be based on full words instead of strings. There needs to be some sort of word boundary between Q2 and other characters for it to be picked up by a simple CONTAINS filter.