问题描述
我的表中有一列被上下文索引.
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;
我正在尝试查询条件为
它返回行.
但是，当我尝试查询条件为
However when i try query with where condition
，它不返回任何行.
有人可以告诉我为什么喜欢工作而包含不工作吗?
Can somebody please tell me why like is working and contains is not?
推荐答案
两个可能的原因-索引可能不同步，并且CONTAINS似乎匹配单词，而LIKE匹配字符串.
Two possible reasons - the index may not be synchronized, and CONTAINS seems to match words while LIKE matches strings.
两个字符串的示例，其中LIKE匹配两个，而CONTAINS都不匹配:
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
默认情况下，CONTEXT索引需要手动同步.您要么需要运行:exec ctx_ddl.sync_index('cidx_mustfixby');，要么需要使用on commit创建索引.
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
这解决了其中一个问题.但是Q234567仍然不匹配.我对Oracle Text不太了解，甚至找不到关于CONTAINS工作原理的简单描述.但这似乎是基于完整的单词而不是字符串.必须有某种词边界在Q2和其他字符之间，以便通过简单的CONTAINS过滤器将其提取.
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 boundarybetween Q2 and other characters for it to be picked up by a simple CONTAINS filter.
这篇关于Oracle Contains无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！