本文共 9522 字，大约阅读时间需要 31 分钟。
Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
这篇介绍分析函数用于分页，且排序字段上包含索引的情况。
Oracle分页查询格式（一）：
Oracle分页查询格式（二）：
Oracle分页查询格式（三）：
Oracle分页查询格式（四）：
Oracle分页查询格式（五）：
Oracle分页查询格式（六）：
Oracle分页查询格式（七）：
Oracle分页查询格式（八）：
Oracle分页查询格式（九）：
Oracle分页查询格式（十）：
Oracle分页查询格式（十一）：
上一篇文章已经介绍了利用分析函数来进行分页的方法，总的来说，除非分页到了最后部分，利用分析函数进行分页的效率还是可以的。
综合来说，除了SQL嵌套可以少写一层外，并没有什么特别的优点来代替标准分页函数的写法。
不过上一篇测试所有的数据都是通过全表扫描得到的，如果在排序字段上存在索引，这两种不同的分页查询效率如何呢，还是继续进行测试：
SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;
表已更改。
SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);
索引已创建。
为了Oracle可以利用这个索引，将索引列置为非空，首先测试标准分页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 <= 20 11 ) 12 WHERE RN >= 11;
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 ptimizer=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;
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: 01: 09.17
Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=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;
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: 10.65
Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=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 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 - ProductionPL/SQL Release 9.2.0.4.0 - ProductionCORE 9.2.0.3.0 ProductionTNS for Linux: Version 9.2.0.4.0 - ProductionNLSRTL 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 - 64biPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for Linux: Version 10.2.0.3.0 - ProductionNLSRTL 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中，用分析函数的方式进行分页，可能会带来严重的性能问题。
转载地址：http://colzl.baihongyu.com/