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

Oracle分页查询语句（一）：

Oracle分页查询语句（二）：

Oracle分页查询语句（三）：

Oracle分页查询语句（四）：

Oracle分页查询语句（五）：

这篇文章通过例子说明分页查询使用的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)

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 过程已成功完成。

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

SQL> SELECT 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 ) 12 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

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 ) 12 WHERE RN BETWEEN 96971 AND 96980;

已选择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 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

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 <= 96980 12 ) 13 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

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