下面测试来至于于一个网友的聊天，关于分页语句。下面在自己的平台简单的测试一把。判断分页语句是否最优化，其实可以简单的看执行计划是否包含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的效率高。
分页语句的简单测试：等您坐沙发呢！