數據庫分頁查詢的排序問題分析

這篇文章主要介紹“數據庫分頁查詢的排序問題分析”，在日常操作中，相信很多人在數據庫分頁查詢的排序問題分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”數據庫分頁查詢的排序問題分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

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信息無法推到查詢內部，導致這種寫法的執行效率很低。

到此，關于“數據庫分頁查詢的排序問題分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！