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排序問題舉例分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！