利用索引提示減少分頁的巢狀層數

今天和同事討論了一下索引掃描避免排序的問題，感覺比較有意思，就簡單總結一下。

首先要強調的是，這並不是標準的或者推薦的一種分頁語句的寫法，這種方法需要對錶、索引的結構有清晰的認識。而且這種方法的限制條件很多。因此，這裡只是單獨討論一下，沒用將其放到分頁專題中去。

下面是分頁標準寫法和利用HINT的方式的對比：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL);

表已建立。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已建立50418行。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已建立。

SQL> SET AUTOT ON
SQL> SET AUTOT ON EXP
SQL> SELECT *
2 FROM
3 (
4 SELECT A.*, ROWNUM RN
5 FROM
6 (
7 SELECT * FROM T ORDER BY NAME
8 ) A
9 WHERE ROWNUM <= 20
10 ) WHERE RN > 10;

ID NAME RN
---------- ------------------------------ ----------
11501 /1023e902_OraCharsetUTFE 11
11502 /1023e902_OraCharsetUTFE 12
46027 /10240eba_GenPropertySequence 13
46145 /10240eba_GenPropertySequence 14
43203 /1025308f_SunTileScheduler 15
44344 /1025308f_SunTileScheduler 16
37617 /10297c91_SAXAttrList 17
38208 /10297c91_SAXAttrList 18
24613 /103a2e73_DefaultEditorKitEndP 19
24614 /103a2e73_DefaultEditorKitEndP 20

已選擇10行。

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

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 860 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 20 | 860 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 45221 | 1324K| 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 45221 | 1324K| 4 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | IND_T_NAME | 21 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

1 - filter("RN">10)
2 - filter(ROWNUM<=20)

Note
-----
- dynamic sampling used for this statement

SQL> SELECT *
2 FROM
3 (
4 SELECT /*+ INDEX(T IND_T_NAME) */ T.*, ROWNUM RN
5 FROM T
6 WHERE ROWNUM <= 20
7 )
8 WHERE RN > 10;

ID NAME RN
---------- ------------------------------ ----------
11501 /1023e902_OraCharsetUTFE 11
11502 /1023e902_OraCharsetUTFE 12
46027 /10240eba_GenPropertySequence 13
46145 /10240eba_GenPropertySequence 14
43203 /1025308f_SunTileScheduler 15
44344 /1025308f_SunTileScheduler 16
37617 /10297c91_SAXAttrList 17
38208 /10297c91_SAXAttrList 18
24613 /103a2e73_DefaultEditorKitEndP 19
24614 /103a2e73_DefaultEditorKitEndP 20

已選擇10行。

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

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 860 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 20 | 860 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 45221 | 1324K| 4 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IND_T_NAME | 45221 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

1 - filter("RN">10)
2 - filter(ROWNUM<=20)

Note
-----
- dynamic sampling used for this statement

對於第二種方法，由於Oracle會採用索引全掃描的方式，因此返回的資料本身就是排好序的，避免的ORDER BY語句，而且可以減少一層巢狀。

更重要的是，對於9i版本，很可能標準SQL的寫法不會使用索引，因此第二種寫法的對於分頁查詢前幾頁具有更高的效率。

對於降序的情況，需要改變HINT，由INDEX修改為INDEX_DESC。

上面是這種寫法的優點，不過這種寫法還存在著很多的缺點和不足。

首先，這種寫法要求排序列必須建立索引，且該列不能為空。否則，Oracle不使用INDEX FULL SCAN執行計劃，則無法保證按照正確的排序返回結果。這就造成了SQL的寫法與表結構、列的NOT NULL約束以及索引的情況有關，SQL的書寫不在透明。而且一旦SQL寫法依賴的結構發生了變化，就會導致SQL得到錯誤的結果。

而且這種寫法對於單表訪問有效，對於多個表連線等複雜情況就無法得到正確的結果了。表連線如果採用HASH JOIN，則會導致原有的排序被破壞，只有排序列的表作為驅動表，則連線方式為NESTED LOOP才能保證最終結果的順序。但是，這只是簡單的情況，對於更多更復雜的執行計劃，很難透過HINT的方式來保證最終結果的順序的。

簡單總結一下，這種方法對於偶然一次的查詢是沒有問題的。但是，不能夠替代標準分頁寫到程式中，因為一旦表結構發生了變化，這個SQL就得到錯誤的結果，而且不會有任何錯誤資訊來提示你，問題已經發生了。