分頁查詢的排序問題（二）

在第一篇文章中介紹過了，當對全表掃描執行ORDER BY STOPKEY操作時，如果排序列是不唯一的，那麼排序結果是不穩定的。

正好PUB上有個帖子在討論這個問題，這裡就簡單描述一下對Oracle排序的一點研究。PUB上的原貼：http://www.itpub.net/showthread.php?s=&threadid=850977

分頁查詢的排序問題：http://yangtingkun.itpub.net/post/468/112274

為了描述問題，首先要再現問題。為了更好的說明問題，在下面的例子中，排序列僅包括兩個不同的值。

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行。

查詢的結果和預期的完全一致。不過上面的推測是以兩個不同的值為基礎推測出來的，當列中有多個不同的鍵值時，演算法會相應複雜得多。