Oracle分页查询格式

来源：互联网发布：app生成器下载软件编辑：程序博客网时间：2024/05/01 01:08

转载于：

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分页查询格式（十一）：http://yangtingkun.itpub.net/post/468/485481

Oracle分页查询格式（十二）：http://yangtingkun.itpub.net/post/468/485496

分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

Oracle分页查询语句（一）：http://yangtingkun.itpub.net/post/468/100278

这篇文章用几个例子来说明分页查询的效率。首先构造一个比较大的表作为测试表：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
457992

首先比较两种分页方法的区别：

SQL> SET AUTOT ON
SQL> COL OBJECT_NAME FORMAT A30
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/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 )
9 )
10 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)
1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7422 physical reads
0 redo size
758 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 FROM T
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)
1 0 VIEW (Cost=864 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

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

二者执行效率相差很大，一个需要8000多逻辑读，而另一个只需要5个逻辑读。观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY，也就是说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。

因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。

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 )
9 WHERE ROWNUM <= 457990
10 )
11 WHERE RN >= 457980;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
7128 XCF_I_HANDLE_STATUS
7126 XCF_P
7127 XCF_U1
7142 XDF
7145 XDF_I_DF_KEY
7146 XDF_I_HANDLE_STATUS
7143 XDF_P
7144 XDF_U1
TEST.YANGTINGKUN
TEST4.YANGTINGKUN
YANGTK.YANGTINGKUN

已选择11行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)
1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7423 physical reads
0 redo size
680 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)
11 rows processed

继续看查询的第二种情况，包含表连接的情况：

SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;

表已创建。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;

表已创建。

SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);

表已更改。

SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
2 REFERENCES T(USERNAME);

表已更改。

SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);

索引已创建。

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

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

创建了T表和T1表，默认情况下，HASH JOIN的效率要比NESTED LOOP高很多：

SQL> SET AUTOT TRACE
SQL> SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER;

已选择96985行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=844 Card=96985 Bytes=46164860)
1 0 HASH JOIN (Cost=844 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=1044)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)

Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
14475 consistent gets
7279 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed

SQL> SELECT /*+ FIRST_ROWS */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER;

已选择96985行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=46164860)
1 0 NESTED LOOPS (Cost=97811 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=87)
4 3 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
117917 consistent gets
7268 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed

但是如果分页查询的内层是这种连接查询的话，使用NESTED LOOP可以更快的得到前N条记录。

下面看一下这种情况下的分页查询情况：

SQL> SELECT USER_ID, USERNAME, NAME
FROM
(
SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM
(
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
FROM T, T1
WHERE T.USERNAME = T1.OWNER
)
WHERE ROWNUM <= 20
)
WHERE RN >= 11;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=20 Bytes=1200)
1 0 VIEW (Cost=830 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
7 physical reads
0 redo size
574 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 /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

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

看上去似乎HASH JOIN效率更高，难道上面说错了。

其实这个现象是由于这个例子的特殊性造成的。T表是根据DBA_USERS创建，这张表很小。HASH JOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的，这就是上面提到的NESTED LOOP比HASH JOIN优势的地方。但是，这个例子中，恰好第一张表很小，对这张表的全扫描的代价极低，因此，显得HASH JOIN效率更高。但是，这不具备共性，如果两张表的大小相近，或者Oracle错误的选择了先扫描大表，则使用HASH JOIN的效率就会低得多。

SQL> SELECT USER_ID, USERNAME, NAME
FROM (SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM (SELECT /*+ ORDERED */
T.USER_ID, T.USERNAME, T1.NAME
FROM T1, T
WHERE T.USERNAME = T1.OWNER)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=951 Card=20 Bytes=1200)
1 0 VIEW (Cost=951 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=951 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)

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

通过HINT提示，让Oracle先扫描大表，这回结果就很明显了。NESTED LOOP的效果要比HASH JOIN好得多。

下面，继续比较一下两个分页操作的写法，为了使结果更具有代表性，这里都采用了FIRST_ROWS提示，让Oracle采用NESTED LOOP的方式来进行表连接：

SQL> SELECT /*+ FIRST_ROWS */
USER_ID, USERNAME, NAME
FROM (SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM (SELECT T.USER_ID, T.USERNAME, T1.NAME
FROM T, T1
WHERE T.USERNAME = T1.OWNER)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

SQL>SELECT /*+ FIRST_ROWS */
USER_ID, USERNAME, NAME
FROM (SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM (SELECT T.USER_ID, T.USERNAME, T1.NAME
FROM T, T1
WHERE T.USERNAME = T1.OWNER))
WHERE RN BETWEEN 11 AND 20;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=97811 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

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

两种写法的效率差别极大。关键仍然是是否能将STOPKEY应用到最内层查询中。

对于表连接来说，在写分页查询的时候，可以考虑增加FIRST_ROWS提示，它有助于更快的将查询结果返回。

其实，不光是表连接，对于所有的分页查询都可以加上FIRST_ROWS提示。不过需要注意的是，分页查询的目标是尽快的返回前N条记录，因此，无论是ROWNUM还是FIRST_ROWS机制都是提高前几页的查询速度，对于分页查询的最后几页，采用这些机制不但无法提高查询速度，反而会明显降低查询效率，对于这一点使用者应该做到心中有数。

最后的例子说明内部循环包含排序的情况：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);

索引已创建。

SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;

表已更改。

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

PL/SQL 过程已成功完成。

下面进行测试包含排序操作的分页查询。可以简单的将查询分为两种不同情况，第一种排序列就是索引列，这种可以利用索引读取，第二种排序列没有索引。

第一种情况又可以细分为：完全索引扫描和通过索引扫描定位到表记录两种情况。

无论是那种情况，都可以通过索引的全扫描来避免排序的产生。看下面的例子：

SQL> SET AUTOT TRACE
SQL> SELECT OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_NAME
FROM (SELECT OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=20 Bytes=1580)
1 0 VIEW (Cost=26 Card=20 Bytes=1580)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=26 Card=6361 Bytes=419826)
4 3 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361 Bytes=108137)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
576 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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (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 (ORDER 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
81 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

由于不能仅仅通过索引扫描得到查询结果，这里Oracle选择了表扫描。这是由于初始化参数设置决定的。因此，建议在分页的时候使用FIRST_ROWS提示。

SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
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
22 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
0 sorts (memory)
0 sorts (disk)
10 rows processed

使用了FIRST_ROWS提示后，Oracle不需要扫描全表，而且避免了排序操作。

下面讨论最后一种情况，排序列不是索引列。这个时候排序不可避免，但是利用给出分页格式，Oracle不会对所有数据进行排序，而是只排序前N条记录。

SQL> SELECT OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP))
WHERE RN BETWEEN 11 AND 20;

已选择10行。

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP)
WHERE ROWNUM <= 20)
WHERE RN >= 11

已选择10行。

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

观察两种不同写法的ORDER BY步骤，一个是带STOPKEY的ORDER BY，另一个不带。在大数据量需要排序的情况下，带STOPKEY的效率要比不带STOPKEY排序的效率高得多。

SQL> INSERT INTO T SELECT T.* FROM T, USER_OBJECTS;

已创建407104行。

SQL> COMMIT;

提交完成。

SQL> SELECT OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

已用时间: 00: 00: 03.78

Statistics
----------------------------------------------------------
268 recursive calls
0 db block gets
6215 consistent gets
6013 physical reads
0 redo size
740 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP))
WHERE RN BETWEEN 11 AND 20;

已选择10行。

已用时间: 00: 00: 11.86

Statistics
----------------------------------------------------------
26 recursive calls
12 db block gets
6175 consistent gets
9219 physical reads
0 redo size
737 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed

观察两个查询语句的执行时间，以及统计信息中的排序信息。对于第一个查询语句，Oracle利用了ORDER BY STOPKEY方式进行排序，排序操作只排序需要的TOP N的数据，因此排序操作放到了内存中，而对于第二个查询语句来说，进行的数据的全排序，排序数据量大，排序操作不得不在磁盘上完成，因此耗时比较多。

通过上面的例子可以看出给出的标准分页查询格式，对于包含排序的操作仍然可以在很大程度上提高分页查询性能。

前面的各种例子已经说明了分页查询语句的标准写法所带来的性能提升。

这里简单总结一下，并简单的说明分页查询语句在何时无法带来性能提升。

分页查询语句之所以可以很快的返回结果，是因为它的目标是最快的返回第一条结果。如果每页有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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME
FROM T
UNION ALL
SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT /*+ INDEX(T) */
OBJECT_ID, OBJECT_NAME
FROM T
UNION ALL
SELECT /*+ INDEX(T) */
OBJECT_ID, OBJECT_NAME
FROM T
ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME
FROM T
GROUP BY OBJECT_ID, OBJECT_NAME
ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID,
OBJECT_NAME,
ROW_NUMBER() OVER(ORDER BY OBJECT_NAME)
FROM T
ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 20)
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

已选择10行。

SQL> SELECT OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME))
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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME)
WHERE ROWNUM <= 12720)
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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME))
WHERE RN BETWEEN 12711 AND 12720;

已选择10行。

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

这篇文章通过例子说明分页查询使用的NESTED LOOP操作，在分页查询翻到最后几页时的性能问题：

SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;

表已创建。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;

表已创建。

SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);

表已更改。

SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
REFERENCES T(USERNAME);

表已更改。

SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);

索引已创建。

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

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

已选择10行。

SQL> SELECT USER_ID, USERNAME, NAME
FROM (SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM (SELECT T.USER_ID, T.USERNAME, T1.NAME
FROM T, T1
WHERE T.USERNAME = T1.OWNER))
WHERE RN BETWEEN 11 AND 20;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

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

在分页查询的前几页，NESTED LOOP操作比HASH JOIN操作效率高得多。

SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T, T1 WHERE USERNAME = OWNER;

COUNT(*)
----------
96985

SQL> SET AUTOT TRACE

SQL> SELECT USER_ID, USERNAME, NAME
FROM (SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM (SELECT T.USER_ID, T.USERNAME, T1.NAME
FROM T, T1
WHERE T.USERNAME = T1.OWNER))
WHERE RN BETWEEN 96971 AND 96980;

已选择10行。

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

对于最后几页，采用HASH JOIN的方式，执行效率几乎没有任何改变，而采用NESTED LOOP方式，则效率严重下降，而且远远低于HASH JOIN的方式。

SQL>
SELECT /*+ FIRST_ROWS */
USER_ID, USERNAME, NAME
FROM (SELECT ROWNUM RN, USER_ID, USERNAME, NAME
FROM (SELECT T.USER_ID, T.USERNAME, T1.NAME
FROM T, T1
WHERE T.USERNAME = T1.OWNER)
WHERE ROWNUM <= 96980)
WHERE RN >= 96971;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980 Bytes=5818800)
1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

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

分页查询一般情况下，很少会翻到最后一篇，如果只是偶尔碰到这种情况，对系统性能不会有很大的影响，但是如果经常碰到这种情况，在设计分页查询时应该给予足够的考虑。

在文章的最后看一下ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异：

SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
458064

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
SQL> SET TIMING ON
SQL>SELECT OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP)
WHERE ROWNUM <= 20)
WHERE RN >= 11;

已选择10行。

已用时间: 00: 00: 00.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)
1 0 VIEW (Cost=13888 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5579 consistent gets
0 physical reads
0 redo size
694 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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP))
WHERE RN BETWEEN 11 AND 20;

已选择10行。

已用时间: 00: 00: 09.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
689 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

对于翻页查询的前几页，采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的优势，那么对于分页查询的最后几页，ORDER BY STOPKEY是否和其他分页查询技术一样，性能比普通方式还要低很多：

SQL> SELECT OBJECT_ID, OBJECT_NAME
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP)
WHERE ROWNUM <= 458060)
WHERE RN >= 458051;

已选择10行。

已用时间: 00: 00: 09.07

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)
1 0 VIEW (Cost=13888 Card=458060 Bytes=42141520)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7933 physical reads
0 redo size
667 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
FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP))
WHERE RN BETWEEN 458051 AND 458060;

已选择10行。

已用时间: 00: 00: 10.01

Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
649 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

出乎意料的是，虽然ORDER BY STOPKEY的方式在分页查询的最后几页性能也有明显的下降，但是在和普通的ORDER BY相比，无论从逻辑读、物理读还是从执行时间上看，二者都属于一个数量级上的。

看来ORDER BY STOPKEY排序方式，在STOPKEY接近排序总量的时候也不会有明显的性能下降。

前面提到了对于集操作和聚集操作，使用标准的分页函数没有太大的意义，下面通过几篇文章专门讨论集操作和聚集操作的情况。这里首先讨论集操作的情况。

当查询需要分页时，大多数情况都会包含排序操作，因为如果缺少排序操作，很难保证分页返回的数据是连续不重复的。

因此这里只考虑包含排序的情况：

SQL> CREATE TABLE T AS SELECT * FROM DBA_TABLES;

表已创建。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_INDEXES;

表已创建。

SQL> CREATE INDEX IND_T_OWNER ON T(OWNER);

索引已创建。

SQL> CREATE INDEX IND_T1_OWNER ON T1(OWNER);

索引已创建。

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

PL/SQL 过程已成功完成。

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT /*+ FIRST_ROWS */
OWNER, NAME
FROM (SELECT ROWNUM RN, OWNER, NAME
FROM (SELECT OWNER, TABLE_NAME NAME
FROM T
UNION ALL
SELECT OWNER, INDEX_NAME NAME FROM T1 ORDER BY OWNER)
WHERE ROWNUM <= 20)
WHERE RN > 10;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$SECTION_GROUP
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_PARTITION

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=20 Bytes=940)
1 0 VIEW (Cost=17 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY STOPKEY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
639 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 */
OWNER, NAME
FROM (SELECT ROWNUM RN, OWNER, NAME
FROM (SELECT OWNER, TABLE_NAME NAME
FROM T
UNION ALL
SELECT OWNER, INDEX_NAME NAME FROM T1 ORDER BY OWNER))
WHERE RN > 10
AND RN <= 20;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$PART_STATS
CTXSYS DR$STATS

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=2877 Bytes=135219)
1 0 VIEW (Cost=17 Card=2877 Bytes=135219)
2 1 COUNT
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
626 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的统计中看到，由于集操作的存在，导致了Oracle没有办法将ROWNUM信息推入到查询内部，导致标准分页方式的效率和其他分页方式效率差别不大。

当存在排序操作，且集操作为UNION ALL操作时，可以改写SQL语句为：

SQL> SELECT /*+ FIRST_ROWS */
OWNER, NAME
FROM (SELECT ROWNUM RN, OWNER, NAME
FROM (SELECT OWNER, NAME
FROM (SELECT OWNER, TABLE_NAME NAME FROM T ORDER BY OWNER)
WHERE ROWNUM <= 20
UNION ALL
SELECT *
FROM (SELECT OWNER, TABLE_NAME NAME FROM T1 ORDER BY OWNER)
WHERE ROWNUM <= 20
ORDER BY OWNER)
WHERE ROWNUM <= 20)
WHERE RN > 10;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_OBJECT
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$SQE
CTXSYS DR$THS
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_BT
CTXSYS DR$SECTION_GROUP
CTXSYS DR$SECTION

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1654 Card=20 Bytes=940)
1 0 VIEW (Cost=1654 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1654 Card=40 Bytes=1360)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=40 Bytes=1360)
5 4 UNION-ALL
6 5 COUNT (STOPKEY)
7 6 VIEW (Cost=826 Card=1157 Bytes=39338)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=1157 Bytes=30082)
9 8 INDEX (FULL SCAN) OF 'IND_T_OWNER' (NON-UNIQUE) (Cost=26 Card=1157)
10 5 COUNT (STOPKEY)
11 10 VIEW (Cost=826 Card=1720 Bytes=58480)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=1720 Bytes=39560)
13 12 INDEX (FULL SCAN) OF 'IND_T1_OWNER' (NON-UNIQUE) (Cost=26 Card=1720)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
631 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推入到UNION ALL操作的每个子查询中，使用这种方法保证查询结果正确的同时，提高了查询的效率。

不过上面给出的改写方法只对包含排序的UNION ALL操作有效。而其他集操作不能使用这种方法，比如UNION操作使用这种方法可能导致查询结果的数量小于查询开始限定的数量。而对于另外两种集操作，这种方法可能会导致错误的查询结果。

本文简单讨论一下包含GROUP BY、DISTINCT、UNIQUE等操作的分页查询语句。

由于包含上述的集操作，Oracle必须处理完所有的数据才会将结果全部的提交给用户。即使包含了ROWNUM，Oracle也只是在处理完所有的数据之后，将需要的数据进行返回。

不过并不是说前面提到的标准写法在这里没有意义，而是最耗时的部分已经处理完毕。通过ROWNUM来限制意义不大。

虽然标准分页的写法对于GROUP BY之类的操作意义不大，但是如果在执行分页之前需要计算总数的话，那么可以对分页的查询语句稍做修改，将计算总数和查询第一页的SQL结合起来，避免一次计算总数的操作。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT *
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT OBJECT_TYPE, CREATED, COUNT(*)
FROM T
GROUP BY OBJECT_TYPE, CREATED
ORDER BY CREATED) A
WHERE ROWNUM < 20)
WHERE RN >= 10;

RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)
1 0 VIEW (Cost=97 Card=19 Bytes=874)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 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 *
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT OBJECT_TYPE, CREATED, COUNT(*)
FROM T
GROUP BY OBJECT_TYPE, CREATED
ORDER BY CREATED) A)
WHERE RN >= 10
AND RN < 20;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1480510)
1 0 VIEW (Cost=97 Card=32185 Bytes=1480510)
2 1 COUNT
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

由于查询操作中GROUP BY操作是耗时最大的SQL，因此标准分页方式在这里所带来的性能提升十分有限。但是，如果在执行分页查询前需要执行COUNT(*)的话，那么可以考虑将COUNT(*)的结果和分页结果一起返回，从而减少了一次计算总数所需的时间。

SQL> SELECT COUNT(*)
FROM (SELECT OBJECT_TYPE, CREATED, COUNT(*)
FROM T
GROUP BY OBJECT_TYPE, CREATED
ORDER BY CREATED);

COUNT(*)
----------
3570

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=97 Card=32185)
3 2 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
377 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)
1 rows processed

SQL> SELECT *
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT OBJECT_TYPE, CREATED, COUNT(*)
FROM T
GROUP BY OBJECT_TYPE, CREATED
ORDER BY CREATED) A
WHERE ROWNUM < 20)
WHERE RN >= 10;

已选择10行。

与上面的两个查询相比，下面的两种方法都可以通过一个SQL语句来实现：

SQL> SELECT *
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT COUNT(*) OVER() CNT, OBJECT_TYPE, CREATED, COUNT(*) CN
FROM T
GROUP BY OBJECT_TYPE, CREATED
ORDER BY CREATED) A
WHERE ROWNUM < 20)
WHERE RN >= 10;

RN CNT OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
10 3570 TABLE 2003-11-13 01:41:01 16
11 3570 CLUSTER 2003-11-13 01:41:02 3
12 3570 INDEX 2003-11-13 01:41:02 31
13 3570 LOB 2003-11-13 01:41:02 2
14 3570 SEQUENCE 2003-11-13 01:41:02 4
15 3570 TABLE 2003-11-13 01:41:02 20
16 3570 INDEX 2003-11-13 01:41:03 16
17 3570 LOB 2003-11-13 01:41:03 6
18 3570 SEQUENCE 2003-11-13 01:41:03 2
19 3570 SYNONYM 2003-11-13 01:41:03 1

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=1121)
1 0 VIEW (Cost=97 Card=19 Bytes=1121)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1480510)
4 3 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=547145)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

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

SQL> SELECT *
FROM (SELECT COUNT(*) OVER() CNT, ROWNUM RN, A.*
FROM (SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
FROM T
GROUP BY OBJECT_TYPE, CREATED
ORDER BY CREATED) A)
WHERE RN >= 10
AND RN < 20;

CNT RN OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
3570 10 TABLE 2003-11-13 01:41:01 16
3570 11 CLUSTER 2003-11-13 01:41:02 3
3570 12 INDEX 2003-11-13 01:41:02 31
3570 13 LOB 2003-11-13 01:41:02 2
3570 14 SEQUENCE 2003-11-13 01:41:02 4
3570 15 TABLE 2003-11-13 01:41:02 20
3570 16 INDEX 2003-11-13 01:41:03 16
3570 17 LOB 2003-11-13 01:41:03 6
3570 18 SEQUENCE 2003-11-13 01:41:03 2
3570 19 SYNONYM 2003-11-13 01:41:03 1

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1898915)
1 0 VIEW (Cost=97 Card=32185 Bytes=1898915)
2 1 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=1062105)
3 2 COUNT
4 3 VIEW (Cost=97 Card=32185 Bytes=1062105)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

第一种方法采用了标准分页方式，效率相对更高一些，但是第一种方法需要对原始SQL进行修改，而第二种方式不需要修改原始SQL，直接在原始SQL外面添加一些代码就可以实现。

Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解决了上一篇文章中提到的GROUP BY操作无法分页的问题。

在10g以前，Oracle的GROUP BY操作必须完全执行完，才能将结果返回给用户。但是Oracle10g增加了GROUP BY STOPKEY执行路径，使得用户在执行GROUP BY操作时，可以根据STOPKEY随时中止正在运行的操作。

这使得标准分页函数对于GROUP BY操作重新发挥了作用。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> CREATE INDEX IND_T_CREATED ON T (CREATED);

索引已创建。

SQL> ALTER TABLE T MODIFY CREATED NOT NULL;

表已更改。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON
SQL> SET TIMING ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT CREATED, COUNT(*)
8 FROM T
9 GROUP BY CREATED
10 ) A
11 WHERE ROWNUM < 20
12 )
13 WHERE RN >= 10;

RN CREATED COUNT(*)
---------- ------------------- ----------
10 2005-12-19 17:07:57 50
11 2005-12-19 17:07:58 36
12 2005-12-19 17:08:24 10
13 2005-12-19 17:08:25 49
14 2005-12-19 17:08:26 66
15 2005-12-19 17:08:27 62
16 2005-12-19 17:08:28 81
17 2005-12-19 17:08:29 82
18 2005-12-19 17:08:33 1
19 2005-12-19 17:08:35 3

已选择10行。

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 3639065582

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 665 | 1 (0)|
|* 1 | VIEW | | 19 | 665 | 1 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 973 | 21406 | 1 (0)|
|* 4 | SORT GROUP BY STOPKEY| | 973 | 7784 | 1 (0)|
| 5 | INDEX FULL SCAN | IND_T_CREATED | 984 | 7872 | 1 (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=10)
2 - filter(ROWNUM<20)
4 - filter(ROWNUM<20)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
385 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 *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT CREATED, COUNT(*)
8 FROM T
9 GROUP BY CREATED
10 ) A
11 )
12 WHERE RN >= 10
13 AND RN < 20;

RN CREATED COUNT(*)
---------- ------------------- ----------
10 2005-12-19 17:09:27 34
11 2005-12-19 17:09:31 29
12 2005-12-19 17:09:40 29
13 2005-12-19 17:09:58 11
14 2005-12-19 17:10:06 6
15 2005-12-19 17:10:12 48
16 2005-12-19 17:10:20 24
17 2005-12-19 17:10:37 8
18 2005-12-19 17:10:40 2
19 2005-12-19 17:10:49 2

已选择10行。

已用时间: 00: 00: 00.06

执行计划
----------------------------------------------------------
Plan hash value: 4036621539

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 973 | 34055 | 14 (36)|
|* 1 | VIEW | | 973 | 34055 | 14 (36)|
| 2 | COUNT | | | | |
| 3 | VIEW | | 973 | 21406 | 14 (36)|
| 4 | HASH GROUP BY | | 973 | 7784 | 14 (36)|
| 5 | INDEX FAST FULL SCAN| IND_T_CREATED | 50359 | 393K| 10 (10)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=10 AND "RN"<20)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
724 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

通过上面的执行计划可以看到，无论使用标准分页函数，还是使用其他方式，Oracle采用的都是10g特有的执行计划GROUP BY STOPKEY和HASH GROUP BY，与9i的GROUP BY相比较，二者的执行效率都很高，不过利用了标准分页模式，Oracle可以将STOPKEY推入到查询最内层，使得Oracle在扫描的开始阶段就将结果集限制住。从查询的逻辑读和执行时间上也可以看出来，GROUP BY STOPKEY的执行效率更高一些。

从这一点上看，Oracle10g的新功能确实使得查询效率得到提高。

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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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;

已选择10行。

已用时间: 00: 00: 18.78

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能不能算一个真正的分页，就是见仁见智的事情了。

上一篇文章已经介绍了利用分析函数来进行分页的方法，总的来说，除非分页到了最后部分，利用分析函数进行分页的效率还是可以的。

综合来说，除了SQL嵌套可以少写一层外，并没有什么特别的优点来代替标准分页函数的写法。

不过上一篇测试所有的数据都是通过全表扫描得到的，如果在排序字段上存在索引，这两种不同的分页查询效率如何呢，还是继续进行测试：

SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;

表已更改。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);

索引已创建。

为了Oracle可以利用这个索引，将索引列置为非空，首先测试标准分页SQL语句：

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

已选择10行。

已用时间: 00: 00: 00.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (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=4584838 Bytes=362202202)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
3 physical reads
0 redo size
578 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中为了使用索引和NESTED LOOP连接方式，一般还要加上FIRST_ROWS提示，现在还没有加上FIRST_ROWS提示，Oracle就使用了索引全扫描代替了全表扫描，而且效率相当的高，只需要0.5秒就返回了结果。

再看分析函数的表现：

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;

已选择10行。

已用时间: 00: 01: 09.17

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)
1 0 VIEW (Cost=826 Card=4584838 Bytes=421805096)
2 1 WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
4 3 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3197229 consistent gets
118443 physical reads
0 redo size
578 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 OBJECT_NAME, OBJECT_ID,
5 ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
6 FROM T
7 )
8 WHERE RN BETWEEN 11 AND 20;

已选择10行。

已用时间: 00: 00: 10.65

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

如果说第一次执行是由于大量物理读没有缓存，导致执行时间达到了1分钟的话，那么第二次执行仍旧高得离谱的三百多万的逻辑读，就很说明问题了。执行时间居然要10秒多，比全表扫描效率还低，看执行计划就知道，这次STOP KEY没有被推到分析函数的窗口排序中，导致Oracle扫描了所有的记录。

这对于分页来说，绝对是不可接受的。不过这是在9i的环境下进行的测试：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

看看10g中Oracle是否解决了这个问题：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

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
---------- ------------------------------
30166 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30166 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:02.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3047187157

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4969K| 436M| | 41652 (1)| 00:09:44 |
|* 1 | VIEW | | 4969K| 436M| | 41652 (1)| 00:09:44 |
|* 2 | WINDOW SORT PUSHED RANK| | 4969K| 132M| 342M| 41652 (1)| 00:09:44 |
| 3 | TABLE ACCESS FULL | T | 4969K| 132M| | 17375 (1)| 00:04:04 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=11 AND "RN"<=20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=20)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
52137 consistent gets
0 physical reads
0 redo size
725 bytes sent via SQL*Net to client
492 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 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
---------- ------------------------------
30165 /1000e8d1_LinkedHashMapValueIt
30166 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30166 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30166 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30166 /1000e8d1_LinkedHashMapValueIt
30165 /1000e8d1_LinkedHashMapValueIt
30166 /1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3257002816

-----------------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes|Cost (%CPU)|Time |
-----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 4969K| 436M| 3679K (1)|14:18:35 |
|* 1| VIEW | | 4969K| 436M| 3679K (1)|14:18:35 |
|* 2| WINDOW NOSORT STOPKEY | | 4969K| 132M| 3679K (1)|14:18:35 |
| 3| TABLE ACCESS BY INDEX ROWID|T | 4969K| 132M| 3679K (1)|14:18:35 |
| 4| INDEX FULL SCAN |IND_T_OBJECT_NAME| 4969K| |11703 (1)|00:02:44 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=11 AND "RN"<=20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=20)

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

10g中表的结构与数据量和9i完全一致，但是默认情况下，Oracle并没有选择使用索引扫描的方式。如果在SQL中加上FIRST_ROWS提示，那么Oracle选择索引扫描，并以接近0秒的速度将结果返回。

对比9i和10g采用分析函数分页的执行计划可以发现，92的执行计划为WINDOW (NOSORT)，而102为WINDOW NOSORT STOPKEY。显然Oracle在10g解决了9i存在的问题，这也是在上一篇文章中提到的，Oracle可能会不断完善分析函数的功能。

如果总结一下，10g中使用分析函数来进行分页，已经没有什么问题了，但是在9i中，用分析函数的方式进行分页，可能会带来严重的性能问题。