Oracle使用collate binary_ci排序结果不一致问题咨询

问题描述 #

在ORDER BY中使用COLLATE BINARY_CI进行排序时，两个仅初始数据顺序不同的查询返回了不一致的结果，预期两次排序结果应完全相同。

第一个查询 #

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','B','b','ä'))
order by name collate binary_ci;

查询结果

第二个查询 #

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','ä','B','b'))
order by name collate binary_ci;

查询结果

两次查询仅ä的初始位置不同，但结果中B和b的排序顺序出现颠倒。

环境信息 #

NLS设置 #

Oracle版本 #

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.18.0.0.0

原因分析 #

BINARY_CI是不区分大小写的二进制排序规则，它仅将大小写字母视为排序等价，但对于排序键相等的记录，Oracle不会强制保证固定的相对顺序——这类记录的排列依赖于输入数据的原始顺序，即排序不具备稳定性。

从NLSSORT的输出可以看到：A和a的排序值均为6100，B和b的排序值均为6200，说明它们在BINARY_CI规则下属于同一排序组。当输入数据中同一排序组内的记录位置变化时，Oracle不会额外维持固定顺序，因此出现了两次结果中B和b顺序颠倒的情况。

解决方法 #

要让同一排序组内的记录保持固定顺序，需在ORDER BY中添加第二排序条件，明确指定相等键记录的排序规则：

方法1：添加原始值的二进制排序（区分大小写） #

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','ä','B','b'))
order by name collate binary_ci, column_value collate binary;

方法2：使用NLSSORT结果作为第一排序键，原始值作为第二键 #

select column_value name,
       nlssort (column_value collate binary_ci) as sort_value
from table(sys.odcivarchar2list('A','a','ä','B','b'))
order by sort_value, column_value;

两种方法都会让同一BINARY_CI排序组内的记录，按照原始值的二进制顺序排列，从而保证无论输入数据顺序如何，最终排序结果一致。

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