在部分选择中不使用NLS_SORT

Question

我已经将nls_sort参数设置为BINARY_AI，以执行不区分大小写的排序。我在系统和会话中设置了它：

alter session set nls_sort='BINARY_AI';
alter system set nls_sort='BINARY_AI' scope=spfile;

此查询的排序是正确的：

SELECT s."Id", s."Label" FROM "Software" s ORDER BY s."Label"

    Id   |  Label
_______________________
10218    | Able2Extract
10217    | AVS Video Recorder

如果使用此查询执行部分select：

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY "Label" ASC) AS MYROW, subSelect.* 
    FROM (
        SELECT s."Id", s."Label" FROM "Software" s
    ) subSelect
) WHERE MYROW BETWEEN 1 AND 100

该排序不区分大小写：

    Id   |  Label
_______________________
10217    | AVS Video Recorder
10218    | Able2Extract

错误在哪里？

Answer 1

您没有在第二个查询中指定的排序顺序，因为您没有ORDER BY子句，因此返回的结果以不确定的顺序返回。

您的MYROW值是根据NLS排序顺序分配的，但是您只使用它来筛选，而不是对结果集进行排序。您可以再次使用它：

SELECT "Id", "Label" FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY "Label" ASC) AS MYROW, subSelect.* 
    FROM (
        SELECT s."Id", s."Label" FROM "Software" s
    ) subSelect
) WHERE MYROW BETWEEN 1 AND 100
ORDER BY MYROW;

或按标签订购：

SELECT "Id", "Label" FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY "Label" ASC) AS MYROW, subSelect.* 
    FROM (
        SELECT s."Id", s."Label" FROM "Software" s
    ) subSelect
) WHERE MYROW BETWEEN 1 AND 100
ORDER BY "Label";

在使用ROW_NUMBER时，您也不需要两个级别的子查询，就像对ROWNUM一样；您可以将其简化为：

SELECT "Id", "Label" FROM (
    SELECT s."Id", s."Label",
      ROW_NUMBER() OVER (ORDER BY s."Label" ASC) AS MYROW
    FROM "Software" s
) WHERE MYROW BETWEEN 1 AND 100
ORDER BY "Label";

内部查询返回MYROW的expect值(比较SQL、代表“二进制”和艾‘)。但这是使用11.2.0.2，它也适用于我在11.2.0.3。目前，我没有一个12c实例可以测试，但其他人可能能够验证那里的行为。

如果您在12.1.0.1中看到了MYROW的错误值，那么您可能会碰到bug 18353141或16934803，两者都位于正确的区域，并在12.1.0.2补丁集发行说明中提到。有关详细信息，请查看我的甲骨文支持。