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的查询。