ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 分页优化(stopkey)

oracle 分页优化(stopkey)

原创 Linux操作系统 作者:JumFlow 时间:2011-07-05 11:28:43 1 删除 编辑



stopkey技术是一种预测技术,完全是为了适应大量数据的出现而出现的

从名称就可以看出,stopkey就是在关键位置上停止,终止的技术,oracle这里指的就是不再继续读取剩余的数据.

======================================================

1.创建测试表

create table test_stopkey as
select * from dba_objects;

2.准备测试数据

insert into test_stopkey
select * from dba_objects;

commit;

SQL> select count(*) from test_stopkey;

COUNT(*)
----------
    200476

3.测试,提取从1000条到2000条数据

第一种情况

select * from 
(select rownum rn,t.* from test_stopkey t)
where rn > 1000 and rn <= 2000;

已用时间: 00: 00: 07.64

执行计划
----------------------------------------------------------
Plan hash value: 594950436

------------------------------------------------------------------------------------
| Id | Operation           | Name         | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |   199K|    36M|   615   (1)| 00:00:08 |
|* 1 | VIEW               |              |   199K|    36M|   615   (1)| 00:00:08 |
|   2 |   COUNT             |              |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST_STOPKEY |   199K|    33M|   615   (1)| 00:00:08 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<=2000 AND "RN">1000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7 recursive calls
          0 db block gets
       2975 consistent gets
          0 physical reads
          0 redo size
      54486 bytes sent via SQL*Net to client
       1111 bytes received via SQL*Net from client
         68 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       1000 rows processed

-----------------------------------------------------------------------------------------------------------------------------------

第二种情况

select * from
(select rownum rn,t.* from test_stopkey t
where rownum <= 2000)
where rn > 1000;

已用时间: 00: 00: 07.35

执行计划
----------------------------------------------------------
Plan hash value: 3382644055

------------------------------------------------------------------------------------
| Id | Operation           | Name         | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              | 2000 |   371K|    11 (28)| 00:00:01 |
|* 1 | VIEW               |              | 2000 |   371K|    11 (28)| 00:00:01 |
|* 2 |   COUNT STOPKEY     |              |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST_STOPKEY |   199K|    33M|    11 (28)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">1000)
   2 - filter(ROWNUM<=2000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7 recursive calls
          0 db block gets
        233 consistent gets
          0 physical reads
          0 redo size
      54486 bytes sent via SQL*Net to client
       1111 bytes received via SQL*Net from client
         68 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       1000 rows processed

第二种情况比第一种一致性读大大减小,主要是把rownum引入到了第二层,注意执行计划中的stopkey,专门是为了提取top n 的需求优化的。

再来句简单的

select * from test_stopkey t where rownum < 5;

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 4076471010

-----------------------------------------------------------------------------------
| Id | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     4 |   372 |     2   (0)| 00:00:01 |
|* 1 | COUNT STOPKEY     |              |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST_STOPKEY |     4 |   372 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<5)


统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          5 consistent gets
          0 physical reads
          0 redo size
       1461 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          4 rows processed

条件中有rownum存在,就会有stopkey优化,那越往后分页是否速度就会一致性读就会越大呢?

select * from
(select rownum rn,t.* from test_stopkey t
where rownum <= 150000)
where rn > 149000;

已用时间: 00: 00: 03.84

执行计划
----------------------------------------------------------
Plan hash value: 3382644055

------------------------------------------------------------------------------------
| Id | Operation           | Name         | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |   150K|    27M|   462   (1)| 00:00:06 |
|* 1 | VIEW               |              |   150K|    27M|   462   (1)| 00:00:06 |
|* 2 |   COUNT STOPKEY     |              |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST_STOPKEY |   150K|    13M|   462   (1)| 00:00:06 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">149000)
   2 - filter(ROWNUM<=150000)


统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2126 consistent gets
          0 physical reads
          0 redo size
      58219 bytes sent via SQL*Net to client
       1111 bytes received via SQL*Net from client
         68 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       1000 rows processed

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25380220/viewspace-701357/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle 约束的状态
请登录后发表评论 登录
全部评论

注册时间:2011-03-03

  • 博文量
    72
  • 访问量
    185719