分頁查詢的排序問題

路途中的人2012發表於2017-06-21

前兩天剛剛看完Tom的EFFECTIVE ORACLE BY DESIGN的第八章,從裡面學到不是東西。同時也發現一個問題:Tom給出的關於分頁以及ROWNUM的時候,幾乎包含了我前幾篇關於分頁文章中的所有關鍵點。

於是,打算補充一篇關於分頁排序問題的文章,這篇文章中的內容是Tom書中沒有的。


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

表已建立。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')

PL/SQL 過程已成功完成。

SQL> SET AUTOT ON EXP
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6236 BCB CCC
6235 AL_U1 CCC
6234 AL_P CCC
6240 BCF_U1 CCC
6239 BCF_P CCC
6238 BCF CCC
6237 BCB_U1 CCC

已選擇10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

上面例子給出的就是分頁查詢的標準寫法,對於查詢前N條資料具有最高的效率。

但是這種分頁排序語句存在一個問題:

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6245 BDF_P CCC
6243 BDF_I_BS_KEY CCC
6241 BCF_U2 CCC
6239 BCF_P CCC
6237 BCB_U1 CCC
6236 BCB CCC
6235 AL_U1 CCC

已選擇10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

對比這次的結果和第一次的結果,就會發現ID為6235的資料出現了兩次。第一次在前10條返回記錄中,6235出現了,而第二次在11到第20條記錄中,6235又出現了。一條資料重複出現兩次,就必然意味著有資料在兩次查詢中都不會出現。

其實造成這個問題的原因很簡單,是由於排序列不唯一造成的。Oracle這裡使用的排序演算法不具有穩定性,也就是說,對於鍵值相等的資料,這種演算法完成排序後,不保證這些鍵值相等的資料保持排序前的順序。

在這個例子中,OWNER列包含了大量鍵值為CCC的資料,而且Oracle的排序演算法不具有穩定性,因此前10行記錄和前20行記錄中鍵值的順序不能保證一致。因此,就造成某些資料會重複出現,而有些資料不會出現的現象。

解決這個問題其實也很簡單。有兩種方法可以考慮。

一,在使用不唯一的欄位排序時,後面跟一個唯一的欄位。

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6235 AL_U1 CCC
6236 BCB CCC
6237 BCB_U1 CCC
6238 BCF CCC
6239 BCF_P CCC
6240 BCF_U1 CCC

已選擇10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6241 BCF_U2 CCC
6242 BDF CCC
6243 BDF_I_BS_KEY CCC
6244 BDF_I_DF_KEY CCC
6245 BDF_P CCC
6246 BDF_U1 CCC
6247 BP CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6249 BP_P CCC
6250 BP_U1 CCC

已選擇10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

一般在排序欄位後面跟一個主鍵就可以了,如果表不存在主鍵,跟ROWID也可以。

這種方法最簡單,且對效能的影響最小。另一種方法就是使用前面給出過多次的BETWEEN AND的方法。

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 1 AND 10;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6238 BCF CCC
6240 BCF_U1 CCC
6242 BDF CCC
6244 BDF_I_DF_KEY CCC
6246 BDF_U1 CCC
6255 BRL_U1 CCC

已選擇10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 11 AND 20;

ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6254 BRL_P CCC
6253 BRL_I_DTS CCC
6252 BRL_I_BS_KEY CCC
6251 BRL CCC
6250 BP_U1 CCC
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6264 CCF CCC
6263 CCB_U1 CCC

已選擇10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)

這種方式由於採用表資料的全排序,每次只取全排序中的某一部分資料,因此不會出現上面提到的重複資料問題。

但是正是由於使用了全排序,而且ROWNUM資訊無法推到查詢內部,導致這種寫法的執行效率很低。對比二種效率的例子,前面分頁查詢的文章中以及有很多了,這裡就不在重複描述了。雖然這種方式也可以避免重複資料問題,但是不推薦使用這種方式。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2141078/,如需轉載,請註明出處,否則將追究法律責任。

相關文章