Oracle 19c中LINGUISTIC搭配BINARY_CI/AI查询结果异常求助

问题描述 #

• 参考Oracle文档Doc ID 2763764.1，文档说明当设置NLS_COMP='LINGUISTIC'且NLS_SORT='BINARY_CI'时嵌套视图查询无结果，设置为'BINARY_AI'则结果正确，但实际测试两种配置均出现异常。
• 测试表test1234abcd包含3条数据，执行查询语句：

  SELECT c1 FROM test1234abcd WHERE c1 LIKE '%Dogs^_%' ESCAPE '^' ORDER BY c1
  

  仅返回2条数据，而Oracle 19c之前版本可正常返回全部3条。需求是实现大小写不敏感查询。

解决方案 #

方案一：查询中显式指定大小写不敏感匹配（不依赖全局NLS参数） #

直接通过函数转换或正则函数实现大小写不敏感，避免NLS参数带来的兼容性问题：

1. 使用UPPER()/LOWER()统一转换：

   SELECT c1 FROM test1234abcd 
   WHERE UPPER(c1) LIKE UPPER('%Dogs^_%') ESCAPE '^' 
   ORDER BY c1
   

2. 使用REGEXP_LIKE带大小写不敏感修饰符（'i'）：

   SELECT c1 FROM test1234abcd 
   WHERE REGEXP_LIKE(c1, '.*Dogs\_.*', 'i') 
   ORDER BY c1
   

   注：正则中直接用\_转义下划线，无需额外指定ESCAPE。

方案二：调整NLS参数并显式指定匹配规则 #

若必须依赖全局NLS参数实现大小写不敏感，可通过以下步骤处理：

1. 确认当前会话的NLS参数：

   SELECT parameter, value FROM nls_session_parameters 
   WHERE parameter IN ('NLS_COMP', 'NLS_SORT');
   

2. 在查询中用NLSSORT函数显式指定排序规则，覆盖会话级参数的潜在影响：

   SELECT c1 FROM test1234abcd 
   WHERE NLSSORT(c1, 'NLS_SORT=BINARY_CI') LIKE NLSSORT('%Dogs^_%', 'NLS_SORT=BINARY_CI') ESCAPE '^' 
   ORDER BY c1;
   

3. 检查Oracle 19c补丁：参考Doc ID 2763764.1，确认是否存在针对该版本的补丁修复了LINGUISTIC模式下LIKE匹配的异常，可联系Oracle官方获取对应补丁。

方案三：通过虚拟列+索引优化频繁的大小写不敏感查询 #

如果需要反复执行这类查询，可创建虚拟列并建立索引提升性能：

1. 添加虚拟列：

   ALTER TABLE test1234abcd ADD c1_upper GENERATED ALWAYS AS (UPPER(c1)) VIRTUAL;
   

2. 创建索引：

   CREATE INDEX idx_test1234abcd_c1_upper ON test1234abcd(c1_upper);
   

3. 查询时使用虚拟列：

   SELECT c1 FROM test1234abcd 
   WHERE c1_upper LIKE UPPER('%Dogs^_%') ESCAPE '^' 
   ORDER BY c1;
   

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