Oracle排序问题举例分析

这篇文章主要讲解了“Oracle排序问题举例分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle排序问题举例分析”吧！

为了描述问题，首先要再现问题。为了更好的说明问题，在下面的例子中，排序列仅包括两个不同的值。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> SELECT TEMPORARY, COUNT(*) FROM T GROUP BY ROLLUP(TEMPORARY);

T   COUNT(*)
- ----------
N      28046
Y         29
      28075

上面已经构造了测试用表，下面进行几个简单的分页查询来定位问题：

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 11
10  )
11  WHERE RN >= 1;

       RN         ID
---------- ----------
        1          1
        2          2
        3          3
        4          4
        5          5
        6          6
        7          7
        8          8
        9          9
       10         10

已选择10行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 111
10  )
11  WHERE RN >= 101;

       RN         ID
---------- ----------
      101        101
      102        102
      103        103
      104        104
      105        105
      106        106
      107        107
      108        108
      109        109
      110        110

已选择10行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 1111
10  )
11  WHERE RN >= 1101;

       RN         ID
---------- ----------
     1101       1101
     1102       1102
     1103       1103
     1104       1104
     1105       1105
     1106       1106
     1107       1107
     1108       1108
     1109       1109
     1110       1110

已选择10行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 11111
10  )
11  WHERE RN >= 11101;

       RN         ID
---------- ----------
    11101       6093
    11102       6094
    11103       6095
    11104       6096
    11105       6097
    11106       6098
    11107       6099
    11108       6100
    11109       6101
    11110       6102

已选择10行。

结果上面4个查询，已经找到了出现问题的地方。在前面几个查询中，ROWNUM的值和ID的值是一致的，只有最后一个查询不满足这个结果。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 6091
10  )
11  WHERE RN >= 6080;

       RN         ID
---------- ----------
     6080       6080
     6081       6081
     6082       6082
     6083       6083
     6084       6084
     6085       6085
     6086       6086
     6087       6087
     6088       6088
     6089       6089
     6090       6090

已选择11行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 6101
10  )
11  WHERE RN >= 6090;

       RN         ID
---------- ----------
     6090       6090
     6091       6091
     6092       6092
     6093       6093
     6094       6094
     6095       6095
     6096       6096
     6097       6097
     6098       6098
     6099       6099
     6100       6100

已选择11行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 6111
10  )
11  WHERE RN >= 6100;

       RN         ID
---------- ----------
     6100       6092
     6101       6093
     6102       6094
     6103       6095
     6104       6096
     6105       6097
     6106       6098
     6107       6099
     6108       6100
     6109       6101
     6110       6102

已选择11行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 6121
10  )
11  WHERE RN >= 6110;

       RN         ID
---------- ----------
     6110       6092
     6111       6093
     6112       6094
     6113       6095
     6114       6096
     6115       6097
     6116       6098
     6117       6099
     6118       6100
     6119       6101
     6120       6102

已选择11行。

通过这4个查询不难看出，问题出在ID=6102这里，当分页查询小于6102时，查询结果中ROWNUM和ID总是相等的。可是一旦分页超过了6102这个值，最后一页的结果就是固定的了——从6092到6102。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 28041
10  )
11  WHERE RN >= 28030;

       RN         ID
---------- ----------
    28030       6092
    28031       6093
    28032       6094
    28033       6095
    28034       6096
    28035       6097
    28036       6098
    28037       6099
    28038       6100
    28039       6101
    28040       6102

已选择11行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 28047
10  )
11  WHERE RN >= 28036;

       RN         ID
---------- ----------
    28036       6093
    28037       6094
    28038       6095
    28039       6096
    28040       6097
    28041       6098
    28042       6099
    28043       6100
    28044       6101
    28045       6102
    28046       6103

已选择11行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY TEMPORARY
 8   ) A
 9   WHERE ROWNUM < 28051
10  )
11  WHERE RN >= 28040;

       RN         ID
---------- ----------
    28040       6097
    28041       6098
    28042       6099
    28043       6100
    28044       6101
    28045       6102
    28046       6103
    28047       6104
    28048       8728
    28049       9075
    28050       8744

已选择11行。

根据刚才对TEMPORARY列的统计，为N的记录有28046个。通过查询可以发现，只要没有翻页到最后一页，查询结果总是6092到6102。

如果将翻页控制到所有为N的记录，会发现最后一条为N的记录是6103。如果继续向下翻页，则开始出现为Y的记录。

记录6103是最后一条为N的记录，记录6104就应该是第一条为Y的记录：

SQL> SELECT ID FROM T WHERE TEMPORARY = 'Y' AND ROWNUM = 1;

       ID
----------
     6104

正如预期中的，6104是第一个为Y的记录，Oracle访问到这里发现了第一个Y，而根据访问的顺序，最后一个为N的结果是6103。

在6104记录之前的分页，结果都是正常的，这是由于Oracle在排序的时候一直没有碰到一个为Y的记录，直到记录6104的出现。由于6104被当作了第一个Y所以6103就被当作了最后一个N。

那么可以推测一下，当查询翻页超过6103后，Oracle把记录6103以及在6103之前的一些记录作为N中的最大值，当要求超过6103时，Oracle会继续向下寻找TEMPORARY为N的记录。这些记录会排序在记录6103等“最大”记录的前面。由于是ORDER BY STOPKEY，当查询得到的记录超过了所要求的记录时，表扫描就停止了。这个时候就会将结果返回给用户。由于分页机制，每次返回的都是查询的最后几条记录。由于STOPKEY的因素，Oracle排序的结果超过返回的记录总数就可以了，因此最为N中最大的6103是超出部分，不会返回，而最大的记录部分就是6092到6102。

这就是为什么当翻页超过一定范围后，为此返回的都是同样的数据的原因。

不过上面的内容完全是根据Oracle的排序结果推断出来的，并没有任何的理论依据，而且这个推断只是一个大概，Oracle具体的算法估计要复杂很多。

为了验证上面的描述：

SQL> SELECT STATUS, COUNT(*) FROM T GROUP BY ROLLUP(STATUS);

STATUS    COUNT(*)
------- ----------
VALID        28075
            28075

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY STATUS
 8   ) A
 9   WHERE ROWNUM < 101
10  )
11  WHERE RN >= 91;

       RN         ID
---------- ----------
       91         91
       92         92
       93         93
       94         94
       95         95
       96         96
       97         97
       98         98
       99         99
      100        100

已选择10行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY STATUS
 8   ) A
 9   WHERE ROWNUM < 1001
10  )
11  WHERE RN >= 991;

       RN         ID
---------- ----------
      991        991
      992        992
      993        993
      994        994
      995        995
      996        996
      997        997
      998        998
      999        999
     1000       1000

已选择10行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY STATUS
 8   ) A
 9   WHERE ROWNUM < 10001
10  )
11  WHERE RN >= 9991;

       RN         ID
---------- ----------
     9991       9991
     9992       9992
     9993       9993
     9994       9994
     9995       9995
     9996       9996
     9997       9997
     9998       9998
     9999       9999
    10000      10000

已选择10行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY STATUS
 8   ) A
 9   WHERE ROWNUM < 28001
10  )
11  WHERE RN >= 27991;

       RN         ID
---------- ----------
    27991      27991
    27992      27992
    27993      27993
    27994      27994
    27995      27995
    27996      27996
    27997      27997
    27998      27998
    27999      27999
    28000      28000

已选择10行。

对于只包含一个值的字段的排序，就不会出现上面翻页结果相同的部分。

下面更新一下STATUS列，制造另一个不同的值，看看查询效果是否和预期的一样：

SQL> UPDATE T SET STATUS = 'VBLID' WHERE ID IN (1000, 1500, 5000);

已更新3行。

SQL> COMMIT;

提交完成。

下面进行查询：

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY STATUS
 8   ) A
 9   WHERE ROWNUM < 111
10  )
11  WHERE RN >= 100;

       RN         ID
---------- ----------
      100        100
      101        101
      102        102
      103        103
      104        104
      105        105
      106        106
      107        107
      108        108
      109        109
      110        110

已选择11行。

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT ROWNUM RN, A.*
 5   FROM
 6   (
 7    SELECT ID FROM T ORDER BY STATUS
 8   ) A
 9   WHERE ROWNUM < 1111
10  )
11  WHERE RN >= 1100;

       RN         ID
---------- ----------
     1100        988
     1101        989
     1102        990
     1103        991
     1104        992
     1105        993
     1106        994
     1107        995
     1108        996
     1109        997
     1110        998

已选择11行。

查询的结果和预期的完全一致。不过上面的推测是以两个不同的值为基础推测出来的，当列中有多个不同的键值时，算法会相应复杂得多。

感谢各位的阅读，以上就是“Oracle排序问题举例分析”的内容了，经过本文的学习后，相信大家对Oracle排序问题举例分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！