Oracle分頁(yè)查詢格式（十一）

Oracle的分頁(yè)查詢語(yǔ)句基本上可以按照本文給出的格式來(lái)進(jìn)行套用。

這篇介紹分析函數(shù)用于分頁(yè)。

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

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

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

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

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

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

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

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

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

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

Oracle從8i推出了分析函數(shù)，9i中分析函數(shù)進(jìn)一步發(fā)展，而且已經(jīng)很穩(wěn)定了。

利用分析函數(shù)的功能，一樣可以實(shí)現(xiàn)分頁(yè)的功能。

首先還是構(gòu)造一張大表，作為分頁(yè)查詢的測(cè)試表：

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

表已創(chuàng)建。

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

PL/SQL 過(guò)程已成功完成。

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

已用時(shí)間:  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行。

已用時(shí)間:  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

仔細(xì)觀察標(biāo)準(zhǔn)分頁(yè)SQL和利用分析函數(shù)的分頁(yè)SQL，這里面有不少有意思的差別。

首先二者得到的結(jié)果是不同的。不過(guò)這其實(shí)并沒(méi)有關(guān)系，因?yàn)檫@本身就是兩個(gè)不同的SQL，而且二者的結(jié)果都是符合查詢條件的。導(dǎo)致這個(gè)現(xiàn)象的原因有兩個(gè)，一個(gè)是兩個(gè)SQL使用的排序算法不同，另一個(gè)是排序字段不唯一。解決這個(gè)問(wèn)題其實(shí)也很簡(jiǎn)單，只需要保證排序結(jié)果唯一就可以了。

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

從SQL的結(jié)構(gòu)上，標(biāo)準(zhǔn)分頁(yè)需要3層嵌套，而分析函數(shù)只需要2層。但是并不意味分析函數(shù)減少了一層嵌套，效率就一定高于標(biāo)準(zhǔn)分頁(yè)，事實(shí)上，多次測(cè)試顯示，標(biāo)準(zhǔn)分頁(yè)似乎還要略快一點(diǎn)。與錯(cuò)誤的分頁(yè)寫(xiě)法相比，這兩個(gè)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行。

已用時(shí)間:  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

上面就是采用了錯(cuò)誤的分頁(yè)寫(xiě)法，使得Oracle排序了所有的數(shù)據(jù)，所需的時(shí)間是正確分頁(yè)寫(xiě)法的6倍以上。

最后將分頁(yè)設(shè)置到最后的部分，檢查兩個(gè)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行。

已用時(shí)間:  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行。

已用時(shí)間:  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

由于表中一個(gè)只有4584838條記錄，因此這個(gè)分頁(yè)基本上是最后一兩頁(yè)了，這可以說(shuō)是一個(gè)極端的例子了，比較二者的效率發(fā)現(xiàn)，標(biāo)準(zhǔn)分頁(yè)效率這次要比分析函數(shù)高將近50%。雖然例子比較極端，不過(guò)也可以說(shuō)明一些問(wèn)題。首先，通過(guò)ORDER BY加ROWNUM方式的排序算法，應(yīng)該和分析函數(shù)的不同。其次，分析函數(shù)的功能很強(qiáng)大，ORDER BY只是其中一個(gè)功能，分析函數(shù)還能完成分區(qū)和窗口操作等更加復(fù)雜的操作，因此效率比單純的排序要低也無(wú)可厚非。當(dāng)然，為了和前面的測(cè)試保持版本的一致性，測(cè)試在920上進(jìn)行，不排除10g或11g對(duì)分析函數(shù)的分頁(yè)做了進(jìn)一步的優(yōu)化。

最后還需要提一句，采用分析函數(shù)的方法，不能沒(méi)有ORDER BY語(yǔ)句，而標(biāo)準(zhǔn)分頁(yè)方式可以。當(dāng)然沒(méi)有ORDER BY能不能算一個(gè)真正的分頁(yè)，就是見(jiàn)仁見(jiàn)智的事情了。