Oracle创建CTXCAT索引开启子串索引后子串搜索无结果

1. 提交插入的测试数据 #

Oracle默认关闭事务自动提交，未提交的数据对全文索引不可见，哪怕是当前会话执行CATSEARCH也可能无法匹配。插入数据后执行提交命令：

COMMIT;

提交后重新执行查询测试。

2. 使用正确的通配符 #

CATSEARCH函数仅支持*作为通配符，不支持SQL原生的%模糊匹配符，你提供的第二条查询语句写法本身不生效，正确的子串匹配写法为：

SELECT * FROM customers WHERE CATSEARCH(NAME, '*ello*', null) > 0;

3. 手动同步索引 #

CTXCAT类型索引默认是事务级自动同步，但若数据库同步配置异常，可手动触发索引同步更新：

EXEC ctx_ddl.sync_index('CUSTOMER_CAT');

4. 确认索引配置生效 #

如果以上操作后仍然无返回结果，可查询索引的分词结果确认子串索引配置是否生效：

SELECT token_text FROM dr$customer_cat$i;

配置正常的情况下，返回结果会包含helloworld的所有连续子串（如ell、ello、ellow等）。如果没有这类子串，说明索引创建时参数未生效，按以下步骤重建索引：

-- 删除旧索引与偏好
DROP INDEX customer_cat;
BEGIN 
  ctx_ddl.drop_preference('catsearch_preferences');
END;
/
-- 重新创建偏好与索引
BEGIN 
  ctx_ddl.create_preference('catsearch_preferences', 'BASIC_WORDLIST'); 
  ctx_ddl.set_attribute('catsearch_preferences','SUBSTRING_INDEX', 'TRUE');
END;
/
CREATE INDEX customer_cat ON CUSTOMERS(NAME) 
  INDEXTYPE IS CTXSYS.CTXCAT 
  PARAMETERS ('WORDLIST catsearch_preferences');

重建完成后重新插入数据、提交、同步索引后再测试即可。

内容的提问来源于stack exchange，提问作者user1851006