Oracle分頁查詢格式（十一）

Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。

這篇介紹分析函數用于分頁。

Oracle分頁查詢格式（一）：http://yangtingkun.itpub.net/post/468/100278

Oracle分頁查詢格式（二）：http://yangtingkun.itpub.net/post/468/101703

Oracle分頁查詢格式（三）：http://yangtingkun.itpub.net/post/468/104595

Oracle分頁查詢格式（四）：http://yangtingkun.itpub.net/post/468/104867

Oracle分頁查詢格式（五）：http://yangtingkun.itpub.net/post/468/107934

Oracle分頁查詢格式（六）：http://yangtingkun.itpub.net/post/468/108677

Oracle分頁查詢格式（七）：http://yangtingkun.itpub.net/post/468/109834

Oracle分頁查詢格式（八）：http://yangtingkun.itpub.net/post/468/224557

Oracle分頁查詢格式（九）：http://yangtingkun.itpub.net/post/468/224409

Oracle分頁查詢格式（十）：http://yangtingkun.itpub.net/post/468/224823

Oracle從8i推出了分析函數，9i中分析函數進一步發展，而且已經很穩定了。

利用分析函數的功能，一樣可以實現分頁的功能。

首先還是構造一張大表，作為分頁查詢的測試表：

SQL> CREATE TABLE T AS     
  2  SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ *
  3  FROM DBA_SEQUENCES A, DBA_OBJECTS B;

表已創建。

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

PL/SQL 過程已成功完成。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   4584838

SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  5     FROM
  6     (
  7             SELECT OBJECT_ID, OBJECT_NAME FROM T
  8             ORDER BY OBJECT_NAME
  9     )
 10     WHERE ROWNUM <= 20
 11  )
 12  WHERE RN >= 11;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant

已選擇10行。

已用時間:  00: 00: 02.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=20 Bytes=1840)
   1    0   VIEW (Cost=34093 Card=20 Bytes=1840)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=34093 Card=4584838 Bytes=362202202)
   4    3         SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      96666  consistent gets
      56154  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant

已選擇10行。

已用時間:  00: 00: 02.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=34093 Card=4584838 Bytes=421805096)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      96666  consistent gets
      56165  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

仔細觀察標準分頁SQL和利用分析函數的分頁SQL，這里面有不少有意思的差別。

首先二者得到的結果是不同的。不過這其實并沒有關系，因為這本身就是兩個不同的SQL，而且二者的結果都是符合查詢條件的。導致這個現象的原因有兩個，一個是兩個SQL使用的排序算法不同，另一個是排序字段不唯一。解決這個問題其實也很簡單，只需要保證排序結果唯一就可以了。

如果從性能上講，二者沒有本質的差別，由于ORDER BY的列上沒有索引，因此兩個SQL都必須對表進行FULL TABLE SCAN。關鍵在于這兩個SQL在能否體現出分頁的特點——STOP KEY。由于用戶當前執行的查詢可能只是返回前100條記錄，那么分頁SQL就沒有必要對所有的數據進行完全的排序，只需要找到最小或最大的100條記錄，就可以返回結果了。在兩個SQL中，都能實現這個功能，其中標準分頁采用的是：SORT (ORDER BY STOPKEY)；而分析函數使用的是：WINDOW (SORT PUSHED RANK)。只要具有將STOP KEY推入到排序操作內的功能，就基本上滿足分頁條件。

從SQL的結構上，標準分頁需要3層嵌套，而分析函數只需要2層。但是并不意味分析函數減少了一層嵌套，效率就一定高于標準分頁，事實上，多次測試顯示，標準分頁似乎還要略快一點。與錯誤的分頁寫法相比，這兩個SQL的效率都是足夠高的，二者之前的差別幾乎可以忽略：

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

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17870 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已選擇10行。

已用時間:  00: 00: 13.18

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=34093 Card=4584838 Bytes=421805096)
   2    1     COUNT
   3    2       VIEW (Cost=34093 Card=4584838 Bytes=362202202)
   4    3         SORT (ORDER BY) (Cost=34093 Card=4584838 Bytes=132960302)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics
----------------------------------------------------------
          0  recursive calls
         50  db block gets
      96666  consistent gets
      96806  physical reads
          0  redo size
        553  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         10  rows processed

上面就是采用了錯誤的分頁寫法，使得Oracle排序了所有的數據，所需的時間是正確分頁寫法的6倍以上。

最后將分頁設置到最后的部分，檢查兩個SQL的性能：

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  5     FROM
  6     (
  7             SELECT OBJECT_ID, OBJECT_NAME FROM T
  8             ORDER BY OBJECT_NAME
  9     )
 10     WHERE ROWNUM <= 4584820
 11  )
 12  WHERE RN >= 4584811;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T

已選擇10行。

已用時間:  00: 00: 12.92

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584820 Bytes=421803440)
   1    0   VIEW (Cost=34093 Card=4584820 Bytes=421803440)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=34093 Card=4584838 Bytes=362202202)
   4    3         SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)
   5    4           TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics
----------------------------------------------------------
          0  recursive calls
         50  db block gets
      96666  consistent gets
      96810  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 4584811 AND 4584820;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T
     28423 xml-extension-type24_T

已選擇10行。

已用時間:  00: 00: 18.78

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=34093 Card=4584838 Bytes=421805096)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)

Statistics
----------------------------------------------------------
          0  recursive calls
         48  db block gets
      96666  consistent gets
      76497  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
         10  rows processed

由于表中一個只有4584838條記錄，因此這個分頁基本上是最后一兩頁了，這可以說是一個極端的例子了，比較二者的效率發現，標準分頁效率這次要比分析函數高將近50%。雖然例子比較極端，不過也可以說明一些問題。首先，通過ORDER BY加ROWNUM方式的排序算法，應該和分析函數的不同。其次，分析函數的功能很強大，ORDER BY只是其中一個功能，分析函數還能完成分區和窗口操作等更加復雜的操作，因此效率比單純的排序要低也無可厚非。當然，為了和前面的測試保持版本的一致性，測試在920上進行，不排除10g或11g對分析函數的分頁做了進一步的優化。

最后還需要提一句，采用分析函數的方法，不能沒有ORDER BY語句，而標準分頁方式可以。當然沒有ORDER BY能不能算一個真正的分頁，就是見仁見智的事情了。