oracle 分页优化（stopkey）

最新推荐文章于 2021-11-05 12:15:32 发布

csdn909034

最新推荐文章于 2021-11-05 12:15:32 发布

阅读量212

点赞数

文章标签：数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25380220/viewspace-701357/