晚上無聊，看到了一個有趣的帖子，可惜帖子到最後也沒給出個答案，實驗的衝動來自於這個帖子。
http://www.itpub.net/viewthread.php?tid=1266783&extra=&page=1

需求：對錶ss按照object_id降序排序，取出第3000條記錄。提供了兩種語句的寫法，看看哪種是最優的。

語句1

語句2

SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT /*+ index_desc(ss ind_id) */*
               FROM SS
               ORDER BY OBJECT_ID DESC) A
)
WHERE RN =3000;

SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT
               *
               FROM SS
               ORDER BY OBJECT_ID DESC) A
      WHERE ROWNUM <= 3000)
WHERE RN > 2999;

SQL> create table ss as select * from dba_objects;
SQL>alter table ss modify object_id not null;（注意這一步對於實驗來說是必須的）
SQL> insert /*+ append */ into ss select * from ss;
執行N次。-------語句略

SQL> create index ind_id on ss(object_id);

分析表:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME       => 'SCOTT',
                              TABNAME       => 'SS',
                              ESTIMATE_PERCENT => 100,
                              METHOD_OPT    => 'for all indexed columns',
                              CASCADE       => TRUE);
END;

語句1，不加rownum的，執行時間： 07.64
SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT /*+ index_desc(ss ind_id) */*
               FROM SS
               ORDER BY OBJECT_ID DESC) A
)
WHERE RN =3000;

執行計劃：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 3794800296

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |       |    1 | 397K|    1 |00:00:07.60 |    398K|
| 2 | COUNT                      |       |    1 |       | 397K|00:00:06.77 |    398K|
| 3 | VIEW                      |       |    1 | 397K| 397K|00:00:05.17 |    398K|
| 4 |    TABLE ACCESS BY INDEX ROWID| SS    |    1 | 397K| 397K|00:00:03.58 |    398K|
| 5 |    INDEX FULL SCAN DESCENDING| IND_ID |    1 | 397K| 397K|00:00:01.19 |    902 |
---------------------------------------------------------------------------------------------------

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

1 - filter("RN"=3000)

語句2，加rownum的：

SELECT *
  FROM (SELECT A.*, ROWNUM RN
      FROM (SELECT
               *
               FROM SS
               ORDER BY OBJECT_ID DESC) A
      WHERE ROWNUM <= 3000)
WHERE RN > 2999;

執行時間：00.17秒，比第一個的時間大大減小。

執行計劃：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 2998430533

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |       |    1 | 3000 |    1 |00:00:00.11 | 3010 |
|*  2 | COUNT STOPKEY             |       |    1 |       | 3000 |00:00:00.10 | 3010 |
| 3 | VIEW                      |       |    1 | 3000 | 3000 |00:00:00.08 | 3010 |
| 4 |    TABLE ACCESS BY INDEX ROWID| SS    |    1 | 397K| 3000 |00:00:00.05 | 3010 |
| 5 |    INDEX FULL SCAN DESCENDING| IND_ID |    1 | 3000 | 3000 |00:00:00.02 |    10 |
---------------------------------------------------------------------------------------------------

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

1 - filter("RN">2999)
2 - filter(ROWNUM<=3000)

對比兩個語句執行計劃的A-ROWS列,BUFFERS列，能清晰的看出第二個語句的效能明顯優於第一個。之所以這樣是因為第二個語句增加了rownum<=3000的條件，導致執行計劃的ID=2多出了一個count stopkey操作，正是它控制著它的子操作可以提前被終止，如果沒有這個count stopkey操作，index full scan descending（id=5）操作需要從右向左掃描所有的索引塊，再根據索引塊讀取leaf節點指向的所有資料塊。由於count stopkey的存在，就可以控制它的子操作（id=5）掃描所需資料塊後就終止操作。（其實5不是2的子，而是重孫，姑且讓我叫子吧）

初學者可能有疑問，需求的內容是按照object_id降序排序取第3000條，如果增加rownum<=3000後，得到的結果會不會不正確，因為其餘的記錄並沒有被排序。這個不會的，因為索引本來就是有序儲存的，我們取的3000條記錄肯定包含了降序排列後的第3000條記錄。

為了初學者更容易理解count stopkey操作，舉個簡單的例子：

SQL> create table test as select * from dba_objects;

表已建立。

SQL> set autotrace traceonly
SQL> select * from test where rownum<2;

執行計劃
----------------------------------------------------------
Plan hash value: 3931117773

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 177 |    4  (50)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |    |    |    |          |       |
| 2 | TABLE ACCESS FULL| TEST | 51101 |  8832K|    4  (50)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter(ROWNUM<2)

統計資訊
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      4  consistent gets
      6  physical reads
      0  redo size

我們可以看到這個執行計劃部分也有一個count stopkey操作。注意觀察id=2的操作是一個全表掃描的操作，但是看到這個查詢的邏輯讀只有4，遠遠小於表的資料塊。之所以會這樣，就是因為count stopkey控制著它的子操作（table access full），在獲得所需記錄數後，父操作就終止了子操作。

如果不提前終止，需要的邏輯讀是4037：

SQL> select * from test;

已選擇49745行。

執行計劃
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 51101 |  8832K| 196 (3)| 00:00:03 |
| 1 |  TABLE ACCESS FULL| TEST | 51101 |  8832K| 196 (3)| 00:00:03 |
--------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
      5  recursive calls
      0  db block gets
   4037  consistent gets
      392  physical reads

再多說幾句，除了count stopkey可以控制子操作的執行程度，有一些操作還可以控制子操作不執行。比如：

SQL> select * from ss where 1=0;

未選定行

已用時間: 00: 00: 00.36
SQL> select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));

Plan hash value: 1965655566

---------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|* 1 | FILTER            |      |      1 |        |      0 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL| SS   |      0 |    397K|      0 |00:00:00.01 |
---------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)

看到ID=2的starts列的值為0，即這個table access full操作根本沒有執行。

還需要說明一點，object_id列的非空限制，在本實驗裡是必須的，因為索引中不儲存空值，如果沒這個非空的條件，執行計劃就不可能採取索引掃描，不過在查詢裡增加object_id is not null也是可以的。

我們可以看看沒有這個非空的限制條件會怎麼樣。

SQL> alter table ss modify object_id  null;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 1785559084

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
|*  1 |  VIEW                   |    |    1 | 3000 |    1 |00:00:02.14 | 5476 |
|*  2 | COUNT STOPKEY       |    |    1 |       | 3000 |00:00:02.14 | 5476 |
| 3 | VIEW                |    |    1 | 397K| 3000 |00:00:02.13 | 5476 |
|*  4 |    SORT ORDER BY STOPKEY|    |    1 | 397K| 3000 |00:00:02.11 | 5476 |
| 5 |    TABLE ACCESS FULL | SS |    1 | 397K| 397K|00:00:00.80 | 5476 |
-------------------------------------------------------------------------------------------

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

1 - filter("RN">2999)
2 - filter(ROWNUM<=3000)
4 - filter(ROWNUM<=3000)

我們看到執行計劃裡看不到了count stopkey操作，但是ID=4行出現了一個sort order by stopkey操作，從ID=5的A-ROWS來看，操作執行了全表掃描（因為索引中不儲存空值，即使增加索引HINT也會全表掃描）。但是ID=3的排序卻由於sort order by stopkey的存在得到了最佳化，只需要排序出前三千條記錄就可以，而不需要把資料全部排序。在這種情況下，rownum的存在，最佳化的只是排序操作，而全表掃描不可避免。因此這種情況下，如果條件允許最好增加一個非空的約束，或者在SQL語句增加條件object_id is not null。

SQL> SELECT *
  2 FROM (SELECT A.*, ROWNUM RN
  3          FROM (SELECT
  4                *
  5                   FROM SS WHEREobject_id IS NOT NULL
  6                ORDER BY OBJECT_ID DESC) A
  7          WHERE ROWNUM <= 3000)
  8 WHERE RN > 2999;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

Plan hash value: 2998430533

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |       |    1 | 3000 |    1 |00:00:00.17 | 3010 | 7 |
|*  2 | COUNT STOPKEY             |       |    1 |       | 3000 |00:00:00.16 | 3010 | 7 |
| 3 | VIEW                      |       |    1 | 3000 | 3000 |00:00:00.14 | 3010 | 7 |
| 4 |    TABLE ACCESS BY INDEX ROWID| SS    |    1 | 397K| 3000 |00:00:00.12 | 3010 | 7 |
|*  5 |    INDEX FULL SCAN DESCENDING| IND_ID |    1 | 3000 | 3000 |00:00:00.08 |    10 | 7 |
------------------------------------------------------------------------------------------------------------

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

1 - filter("RN">2999)
2 - filter(ROWNUM<=3000)
5 - filter("OBJECT_ID" IS NOT NULL)

分頁查詢初接觸

相關文章

語句1	語句2
SELECT * FROM (SELECT A., ROWNUM RN FROM (SELECT /+ index_desc(ss ind_id) / FROM SS ORDER BY OBJECT_ID DESC) A ) WHERE RN =3000;	SELECT * FROM (SELECT A., ROWNUM RN FROM (SELECT FROM SS ORDER BY OBJECT_ID DESC) A WHERE ROWNUM <= 3000) WHERE RN > 2999;