下面测试来至于于一个网友的聊天，关于分页语句。下面在自己的平台简单的测试一把。判断分页语句是否最优化，其实可以简单的看执行计划是否包含SORT ORDER BY STOPKEY这一列，如果有这一列，意味着查询完所有的数据，在对数据进行排序的时，根据rownum的值来终止排序过程。分页语句其实不仅仅要实现功能上的分页功能，也要使用利用rownum来终止对数据的访问。
1，环境介绍
www.htz.pw > host uname -a
windows32 luoping 2.6.1 7601 i686-pc Intel unknown MinGW
www.htz.pw > select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
2，创建测试数据
利用dba_objects表来创建测试表
www.htz.pw > create user htz identified by oracle;
User created.
www.htz.pw > grant dba to htz;
Grant succeeded.
www.htz.pw > create table htz.page1 as select * from dba_objects where rownum<20;
Table created.
www.htz.pw > create table htz.pages2 as select * from dba_objects;
Table created.
www.htz.pw > create index htz.ind_pages2_1 on htz.pages2(object_id,owner);
Index created.
3，配置参数
请注意是在会话级别修改参数为all,用于收集执行计划每一步实际还回的行数。也可以通过增加提示来实现。在优化OLTP环境SQL时候，如果不知道执行计划中那一步消耗大量的资源，也可以通过配置此参数，来收集详细的信息。
www.htz.pw > alter session set statistics_level=all;
Session altered.
4，分页语句执行
这里按a.object_id排序，取第5行到第10行之间的数据
4.1 只实现分页功能
select owner, object_id, object_type, object_name, rn
from (select owner, object_id, object_type, object_name, rownum rn
from (select /*+ use_nl(a b)*/
a.owner, a.object_id, a.object_type, b.object_name
from htz.page1 a, htz.pages2 b
where a.owner = b.owner
and b.object_id > 50000
order by a.object_id)
where rownum < 10)
where rn > 5
SQL> @plan_by_last.sql
SQL> set echo off
Enter value for sqlid:
old 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('&sqlid',null),null,'ALLSTATS LAST'))
new 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('',null),null,'ALLSTATS LAST'))
Plan hash value: 1735455317
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.13 |
|* 1 | VIEW | | 1 | 9 | 4 |00:00:00.13 |
|* 2 | COUNT STOPKEY | | 1 | | 9 |00:00:00.13 |
| 3 | VIEW | | 1 | 208K| 9 |00:00:00.13 |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 208K| 9 |00:00:00.13 |
| 5 | NESTED LOOPS | | 1 | | 79933 |00:00:00.12 |
| 6 | NESTED LOOPS | | 1 | 208K| 79933 |00:00:00.05 |
| 7 | TABLE ACCESS FULL | PAGE1 | 1 | 19 | 19 |00:00:00.01 |
|* 8 | INDEX RANGE SCAN | IND_PAGES2_1 | 19 | 1051 | 79933 |00:00:00.04 |
| 9 | TABLE ACCESS BY INDEX ROWID| PAGES2 | 79933 | 10981 | 79933 |00:00:00.03 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">5)
2 - filter(ROWNUM<10)
4 - filter(ROWNUM<10)
8 - access("B"."OBJECT_ID">50000 AND "A"."OWNER"="B"."OWNER" AND "B"."OBJECT_ID" IS NOT NULL)
filter("A"."OWNER"="B"."OWNER")
通过执行计划可以到，还回4条记录，但是访问了所有的数据。IND_PAGES2_1被执行了19次，等于PAGE1的总行数。PAGES2回表79933次。
4.2 利用rownum终止对数据的访问
利用rownum终止对数据的访问有几个前提条件：1，order by后面的列，只能是驱动表中的列。2，执行计划要走NL的方式，3，order by后面列所在的表为驱动表。4，SQL语句中不能包括minus,union,union all等待关键字。5，驱动表在where中的所有列需要都在索引中，select不要求。
创建索引
www.htz.pw > create index htz.ind_page1_2 on htz.page1(object_id,owner);
Index created.
www.htz.pw > select owner, object_id, object_type, object_name, rn
2 from (select owner, object_id, object_type, object_name, rownum rn
3 from (select /*+ use_nl(a b) index(a ind_page1_2)*/
4 a.owner, a.object_id, a.object_type, b.object_name
5 from htz.page1 a, htz.pages2 b
6 where a.owner = b.owner
7 and b.object_id > 50000
8 order by a.object_id)
9 where rownum < 10)
10 where rn > 5
11 ;
www.htz.pw > @plan_by_last.sql
www.htz.pw > set echo off
Enter value for sqlid:
old 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('&sqlid',null),null,'ALLSTATS LAST'))
new 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(nvl('',null),null,'ALLSTATS LAST'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------SQL_ID 1nvjmktk0rpqm, child number 0
-------------------------------------
select owner, object_id, object_type, object_name, rn from (select
owner, object_id, object_type, object_name, rownum rn from
(select /*+ use_nl(a b) index(a ind_page1_2)*/
a.owner, a.object_id, a.object_type, b.object_name
from htz.page1 a, htz.pages2 b where a.owner = b.owner
and b.object_id > 50000 order by
a.object_id) where rownum < 10) where rn > 5
Plan hash value: 2290584084
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 7 |
|* 1 | VIEW | | 1 | 9 | 4 |00:00:00.01 | 7 |
|* 2 | COUNT STOPKEY | | 1 | | 9 |00:00:00.01 | 7 |
| 3 | VIEW | | 1 | 30014 | 9 |00:00:00.01 | 7 |
| 4 | NESTED LOOPS | | 1 | | 9 |00:00:00.01 | 7 |
| 5 | NESTED LOOPS | | 1 | 30014 | 9 |00:00:00.01 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| PAGE1 | 1 | 19 | 1 |00:00:00.01 | 2 |
| 7 | INDEX FULL SCAN | IND_PAGE1_2 | 1 | 19 | 1 |00:00:00.01 | 1 |
|* 8 | INDEX RANGE SCAN | IND_PAGES2_1 | 1 | 1 | 9 |00:00:00.01 | 3 |
| 9 | TABLE ACCESS BY INDEX ROWID | PAGES2 | 9 | 1580 | 9 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">5)
2 - filter(ROWNUM<10)
8 - access("B"."OBJECT_ID">50000 AND "A"."OWNER"="B"."OWNER" AND "B"."OBJECT_ID" IS NOT NULL)
filter("A"."OWNER"="B"."OWNER")
Note
-----
- dynamic sampling used for this statement (level=2)
39 rows selected.
这里已经没有看到SORT ORDER BY STOPKEY这一行，并且被驱动表执行次数为rownum指定的还回的行数。说明通过rownum已经实现终止对更多数据的访问。
需要注意的表连接方法走的是NL，如果rownum的取值大于驱动表还回的总行数的1/2的时候，我们需要更改一下SQL，可以走索引的desc。如果rownum在1/2左右的时候，此方法的效率可能还没有走HASH的效率高。
分页语句的简单测试：等您坐沙发呢！