Oracle索引含VARCHAR2列时排序失效？如何规避显式SORT操作

你遇到的显式排序问题确实由VARCHAR2的排序规则和索引存储规则不匹配导致：默认Oracle B树索引按VARCHAR2列的二进制值排序存储，而如果会话NLS_SORT参数不为BINARY时，SQL的ORDER BY会按语言规则（如大小写不敏感、拼音/笔画排序等）执行，两者顺序不一致，Oracle无法直接复用索引的有序性，必须额外执行排序操作。

以下两种方案均可适配Oracle 12.1版本，无需修改业务逻辑的核心语义即可去掉显式排序操作：

方案1：创建匹配排序规则的函数索引 #

如果业务需要保留当前NLS_SORT的语言排序规则，不需要调整查询写法，可以创建基于NLSSORT的函数索引，让索引的排序顺序和查询要求的排序顺序一致。
举个例子，如果你的会话NLS_SORT参数为大小写不敏感的BINARY_CI，索引创建语句如下：

CREATE INDEX ix_test2_nls ON test(NLSSORT(t, 'NLS_SORT=BINARY_CI'), id);

如果使用中文拼音排序规则SCHINESE_PINYIN_M，对应修改参数值即可：

CREATE INDEX ix_test2_nls ON test(NLSSORT(t, 'NLS_SORT=SCHINESE_PINYIN_M'), id);

创建完成后执行原查询，Oracle会自动匹配该函数索引，直接走索引范围扫描，不需要额外排序。

方案2：调整查询排序规则匹配默认索引 #

如果业务允许使用二进制排序，可以通过修改会话参数或者调整ORDER BY子句的方式，让排序规则和默认索引的二进制存储顺序一致，无需修改索引定义。

• 方式1：调整会话参数
  执行查询前先修改当前会话的排序规则：

ALTER SESSION SET NLS_SORT=BINARY;

再执行原查询即可直接复用IX_TEST2索引的有序性，去掉显式排序。

• 方式2：显式指定ORDER BY的排序逻辑
  不需要修改会话参数，直接在ORDER BY子句中指定按二进制排序即可：

SELECT *
    FROM test
   WHERE     t = 'X'
         AND id > 100
ORDER BY NLSSORT(t, 'NLS_SORT=BINARY'), id;

调整后执行计划会直接走IX_TEST2的范围扫描，无需额外排序。

任选一种适配业务场景的方案调整后，重新查看执行计划，SORT ORDER BY操作会被移除，完全复用索引的有序性。

内容的提问来源于stack exchange，提问作者D. Mika