oracle 分頁優化（stopkey）

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