• 博客访问: 181934
  • 博文数量: 72
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-03 13:31
个人简介

暂无介绍

文章分类
文章存档

2012年(6)

2011年(66)

我的朋友

分类: Linux操作系统

2011-07-05 11:28:43



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

阅读(4800) | 评论(1) | 转发(1) |
给主人留下些什么吧!~~

duanjian0832015-09-17 11:15:26

good job!

评论热议
请登录后评论。

登录 注册