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

Oracle分页查询语句（一）：

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

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 A30SQL> 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;

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