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能不能算一個真正的分頁，就是見仁見智的事情了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2141086/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分頁查詢格式Oracle
- Oracle分頁查詢格式（十三）Oracle
- Oracle分頁查詢格式（十二）Oracle
- Oracle分頁查詢格式（八）Oracle
- Oracle分頁查詢格式（九）Oracle
- Oracle分頁查詢格式（十）Oracle
- oracle分頁查詢Oracle
- Oracle的分頁查詢Oracle
- oracle分頁查詢語句Oracle
- Oracle分頁查詢之order byOracle
- oracle sql分頁查詢（一）OracleSQL
- Oracle分頁查詢語句（七）Oracle
- Oracle分頁查詢語句（六）Oracle
- Oracle分頁查詢語句（五）Oracle
- Oracle分頁查詢語句（四）Oracle
- Oracle分頁查詢語句（三）Oracle
- Oracle分頁查詢語句（二）Oracle
- Oracle分頁查詢語句（一）Oracle
- Oracle總結【SQL細節、多表查詢、分組查詢、分頁】OracleSQL
- 有關Oracle分頁查詢語句Oracle
- Elasticsearch 分頁查詢Elasticsearch
- ssh 分頁查詢
- oracle 資料分頁查詢 (轉貼收集)Oracle
- 最新+最簡單Oracle分頁查詢法Oracle
- Oracle資料庫中的分頁查詢Oracle資料庫
- MySQL的分頁查詢MySql
- ThinkPhp框架：分頁查詢PHP框架
- 分頁查詢優化優化
- NET 集合分頁查詢
- MySQL 多表查詢分頁MySql
- 分頁查詢重構
- Lucene的分頁查詢
- Oracle_Day2 查詢練習,以及分頁Oracle
- elasticsearch查詢之大資料集分頁查詢Elasticsearch大資料
- 分庫分表後的分頁查詢
- MySQL、Oracle和SQL Server的分頁查詢語句MySqlOracleServer
- indexdb實現分頁查詢Index
- AntDesignBlazor示例——分頁查詢Blazor