Geek asks and answers

Oracle Contains not working

I have a column in my table indexed by context.

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

And I'm trying to fulfill a request with a condition

And must_fix_by LIKE 'Q2%'

and it returns the rows.

However, when I try to fulfill a request with a condition

And contains (must_fix_by, 'Q2')> 0

and it does not return any rows.

Can someone please tell me why, how it works and contains, no?

+5
source share
1 answer

Two possible reasons: the index cannot be synchronized, but CONTAINScorresponds to words, and LIKEcorresponds to lines.

An example of two lines where it LIKEmatches both, but CONTAINSdoes not match either of these:

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

CONTEXT . : exec ctx_ddl.sync_index('cidx_mustfixby');, on commit.

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

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

. Q234567 - . Oracle Text, , CONTAINS. , , . - Q2 , CONTAINS.

+5

More articles:


All Articles