当前位置: 首页 > 调优 > 正文

分页语句的简单测试

下面测试来至于于一个网友的聊天,关于分页语句。下面在自己的平台简单的测试一把。判断分页语句是否最优化,其实可以简单的看执行计划是否包含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终止对数据的访问有几个前提条件:1order by后面的列,只能是驱动表中的列。2,执行计划要走NL的方式,3order by后面列所在的表为驱动表。4SQL语句中不能包括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。如果rownum1/2左右的时候,此方法的效率可能还没有走HASH的效率高。

本文固定链接: http://www.htz.pw/2014/09/21/%e5%88%86%e9%a1%b5%e8%af%ad%e5%8f%a5%e7%9a%84%e7%ae%80%e5%8d%95%e6%b5%8b%e8%af%95.html | 认真就输

该日志由 huangtingzhong 于2014年09月21日发表在 调优 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 分页语句的简单测试 | 认真就输
关键字: , ,