今天和同事讨论了一下索引扫描避免排序的问题，感觉比较有意思，就简单总结一下。
首先要强调的是，这并不是标准的或者推荐的一种分页语句的写法，这种方法需要对表、索引的结构有清晰的认识。而且这种方法的限制条件很多。因此，这里只是单独讨论一下，没用将其放到分页专题中去。
下面是分页标准写法和利用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就得到错误的结果，而且不会有任何错误信息来提示你，问题已经发生了。
