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

前面的各種例子已經說明了分頁查詢語句的標準寫法所帶來的性能提升。

這里簡單總結一下，并簡單的說明分頁查詢語句在何時無法帶來性能提升。

分頁查詢語句之所以可以很快的返回結果，是因為它的目標是最快的返回第一條結果。如果每頁有20條記錄，目前翻到第5頁，那么只需要返回前100條記錄都可以滿足查詢的要求了，也許還有幾萬條記錄也符合查詢的條件，但是由于分頁的限制，在當前的查詢中可以忽略這些數據，而只需盡快的返回前100條數據。這也是為什么在標準分頁查詢語句中經常會使用FIRST_ROWS提示的原因。

對于行操作，可以在得到結果的同時將結果直接返回給上一層調用。但是對于結果集操作，Oracle必須得到結果集中所有的數據，因此分頁查詢中所帶的ROWNUM信息不起左右。如果最內層的子查詢中包含了下面這些操作中的一個以上，則分頁查詢語句無法體現出任何的性能優勢：UNION、UNION ALL、MINUS、INTERSECT、GROUP BY、DISTINCT、UNIQUE以及聚集函數如MAX、MIN和分析函數等。

除了這些操作以外，分頁查詢還有一個很明顯的特點，就是處理的頁數越小，效率就越高，越到后面，查詢速度越慢。

分頁查詢用來提高返回速度的方法都是針對數據量較小的前N條記錄而言。無論是索引掃描，NESTED LOOP連接，還是ORDER BY STOPKEY，這些方法帶來性能提升的前提都是數據量比較小，一旦分頁到了最后幾頁，會發現這些方法不但沒有辦法帶來性能的提升，而且性能比普通查詢還要低得多。這一點，在使用分頁查詢的時候，一定要心里有數。

最后看幾個例子：

首先看看UNION ALL、GROUP BY以及分析函數使外層的ROWNUM限制對內層查詢無效。

SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */ 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 ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
597 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)
0 sorts (disk)
10 rows processed

這是分頁查詢ROWNUM起作用的情況，下面看看如果內層查詢包括了集操作時的情況：

SQL> SELECT /*+ FIRST_ROWS */ 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 UNION ALL
9 SELECT OBJECT_ID, OBJECT_NAME FROM T
10 ORDER BY OBJECT_NAME
11 )
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=85 Card=20 Bytes=1840)
1 0 VIEW (Cost=85 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=85 Card=12722 Bytes=1005038)
4 3 SORT (ORDER BY STOPKEY) (Cost=18 Card=12722 Bytes=267162)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)
7 5 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
322 consistent gets
0 physical reads
0 redo size
546 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 /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT /*+ INDEX(T) */ OBJECT_ID, OBJECT_NAME FROM T
8 UNION ALL
9 SELECT /*+ INDEX(T) */ OBJECT_ID, OBJECT_NAME FROM T
10 ORDER BY OBJECT_NAME
11 )
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1719 Card=20 Bytes=1840)
1 0 VIEW (Cost=1719 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1719 Card=12722 Bytes=1005038)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=12722 Bytes=267162)
5 4 UNION-ALL
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
7 6 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
9 8 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24004 consistent gets
0 physical reads
0 redo size
546 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

如果說全表掃描的情況下，ROWNUM不起作用效果也不是很明顯的話，通過使用HINT，讓Oracle使用索引掃描，這時ROWNUM不起作用的效果就相當驚人了。

SQL> SELECT /*+ FIRST_ROWS */ 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 GROUP BY OBJECT_ID, OBJECT_NAME
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=43 Card=20 Bytes=1840)
1 0 VIEW (Cost=43 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (GROUP BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
673 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 /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME)
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 WINDOW (BUFFER) (Cost=826 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
6 5 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12002 consistent gets
0 physical reads
0 redo size
597 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> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
12722

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

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
597 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)
0 sorts (disk)
10 rows processed

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
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=6361 Bytes=585212)
1 0 VIEW (Cost=43 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
597 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

首先看一下，分頁查詢的性能比不使用ROWNUM的情況要強很多，但是，如果將分頁的范圍設置到12710和12720之間，這時候再來對比一下兩種查詢的效率。

SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 12720
12 )
13 WHERE RN >= 12711;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=6361 Bytes=585212)
1 0 VIEW (Cost=826 Card=6361 Bytes=585212)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12001 consistent gets
0 physical reads
0 redo size
612 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)
0 sorts (disk)
10 rows processed

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
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 )
12 WHERE RN BETWEEN 12711 AND 12720;

已選擇10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=6361 Bytes=585212)
1 0 VIEW (Cost=43 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
612 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

不難發現，對于第二個查詢，效率和第一次執行完全一樣，但是分頁查詢的效率則大大的下降，而且此時的效率遠遠低于沒有使用ROWNUM的查詢。