oracle 分页优化（stopkey）

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

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

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

1.创建测试表

create table vast as select * from dba_objects;

2.准备测试数据 开启执行计划

SQL> insert into vast select * from dba_objects;

72554 rows created.

3.测试，提取从500条到1000条数据

SQL> set autotrace on explain

第一种情况

SQL> select * from (select rownum r,a.* from vast a) where r>500 and r<1000;

已用时间: 00: 00: 07.13

执行计划

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

Plan hash value: 2564315607

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |      |   127K|    26M|   562   (1)| 00:00:07 |

|*  1 |  VIEW               |      |   127K|    26M|   562   (1)| 00:00:07 |

|   2 |   COUNT             |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| VAST |   127K|    25M|   562   (1)| 00:00:07 |

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

Predicate Information (identified by operation id):

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

   1 - filter("R"<1000 AND "R">500)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

          0  recursive calls

          0  db block gets

       2102  consistent gets

          0  physical reads

          0  redo size

      29806  bytes sent via SQL*Net to client

        886  bytes received via SQL*Net from client

         35  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        499  rows processed

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

第二种情况

 select * from (select rownum r,a.* from vast a where rownum<1000) where r>500;

已用时间: 00: 00: 07.35

执行计划
----------------------------------------------------------

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

Plan hash value: 1948416614

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |      |   999 |   214K|     7  (15)| 00:00:01 |

|*  1 |  VIEW               |      |   999 |   214K|     7  (15)| 00:00:01 |

|*  2 |   COUNT STOPKEY     |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| VAST |   127K|    25M|     7  (15)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("R">500)

   2 - filter(ROWNUM<1000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

          0  recursive calls

          0  db block gets

        50  consistent gets

          0  physical reads

          0  redo size

      29806  bytes sent via SQL*Net to client

        886  bytes received via SQL*Net from client

         35  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        499  rows processed

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

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

再来句简单的

select * from vast where rownum < 5;

已用时间: 00: 00: 00.04

执行计划

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

Plan hash value: 338940916

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     4 |   828 |     3  (34)| 00:00:01 |

|*  1 |  COUNT STOPKEY     |      |       |       |            |          |

|   2 |   TABLE ACCESS FULL| VAST |   127K|    25M|     3  (34)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter(ROWNUM<5)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

          8  recursive calls

          0  db block gets

        129  consistent gets

          0  physical reads

          0  redo size

       1896  bytes sent via SQL*Net to client

        523  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 r,a.* from vast a where rownum <= 70000) where r > 69000;

已用时间: 00: 00: 04.24

执行计划

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

Plan hash value: 1948416614

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |      | 70000 |    14M|   310   (1)| 00:00:04 |

|*  1 |  VIEW               |      | 70000 |    14M|   310   (1)| 00:00:04 |

|*  2 |   COUNT STOPKEY     |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| VAST |   127K|    25M|   310   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

   1 - filter("R">69000)

   2 - filter(ROWNUM<=70000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

          7  recursive calls

          0  db block gets

       1189  consistent gets

          0  physical reads

          0  redo size

      64623  bytes sent via SQL*Net to client

       1249  bytes received via SQL*Net from client

         68  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1000  rows processed

看来 stopkey 还是要注意 不能在rownum过大的时候使用。