Oracle中CONTAINS查询遇A/D字符返回全部行的问题咨询

问题根源 #

核心原因：A和D是Oracle全文索引默认停用词表中的停用词

Oracle的全文索引会自动忽略停用词（通常是高频无意义词汇，比如单个字母、冠词等）。当你执行CONTAINS(ADDRESS, 'NARROW WATER CASTLE A')时，因为A被标记为停用词，查询会被简化为仅搜索'NARROW WATER CASTLE'——而你的所有地址行都包含这三个词，因此返回全部结果。

而B、C、E不在默认停用词表中，所以查询会精确匹配完整短语，只返回对应行。

验证方法 #

执行以下SQL检查默认停用词表，确认A和D是否在其中：

SELECT STOPWORD 
FROM CTXSYS.STOPWORDS 
WHERE STOPLIST_NAME = 'DEFAULT_STOPLIST' 
  AND STOPWORD IN ('A', 'D');

如果返回结果包含'A'和'D'，即可确认是停用词导致的问题。

解决办法 #

方法1：查询时强制包含停用词 #

在CONTAINS的搜索字符串后添加INCLUDE STOPWORDS选项，强制Oracle不忽略停用词：

-- 搜索A的正确查询
SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE A') INCLUDE STOPWORDS) > 0;

-- 搜索D的正确查询
SELECT ADDRESS FROM E_TRAPLC_N A 
WHERE contains (ADDRESS, UPPER ('NARROW WATER CASTLE D') INCLUDE STOPWORDS) > 0;

方法2：自定义停用词表（永久解决） #

如果需要长期避免这个问题，可以创建自定义停用词表，移除A和D后重新构建索引：

1. 创建基于默认停用词表的自定义停用词表：

CREATE STOPLIST my_custom_stoplist FROM DEFAULT_STOPLIST;

2. 从自定义停用词表中移除A和D：

ALTER STOPLIST my_custom_stoplist DROP 'A';
ALTER STOPLIST my_custom_stoplist DROP 'D';

3. 重建全文索引，指定使用自定义停用词表（假设索引名为idx_e_traplc_address）：

ALTER INDEX idx_e_traplc_address REBUILD PARAMETERS('STOPLIST my_custom_stoplist');

方法3：使用空停用词表（不推荐） #

如果不需要任何停用词过滤，可以直接使用空停用词表创建/重建索引，但这会增加索引体积和查询耗时，仅适合特殊场景：

-- 重建索引时使用空停用词表
ALTER INDEX idx_e_traplc_address REBUILD PARAMETERS('STOPLIST CTXSYS.EMPTY_STOPLIST');

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