INDEX FAST FULL SCAN,INDEX FULL SCAN与排序

SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table t as select * from dba_objects where 1=0;
Table created.
SQL> create index ind_object_id on t(object_id);
Index created.
SQL> insert into t select * from dba_objects ;
72526 rows created.
SQL> commit;
Commit complete.
SQL> alter table t modify(object_id not null);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
SQL> select object_id from t where rownum<11  ;
 OBJECT_ID
----------
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10
	11
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3425322059
---------------------------------------------------------------------------------------
| Id  | Operation	      | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	      |    10 |    50 |     2	(0)| 00:00:01 |
|*  1 |  COUNT STOPKEY	      | 	      |       |       | 	   |	      |
|   2 |   INDEX FAST FULL SCAN| IND_OBJECT_ID |    10 |    50 |     2	(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<11)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  9  consistent gets
	  0  physical reads
	  0  redo size
	645  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 10  rows processed
SQL> select /*+ index(t,ind_object_id) */ object_id from t where rownum<11  ;
 OBJECT_ID
----------
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10
	11
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2312333252
----------------------------------------------------------------------------------
| Id  | Operation	 | Name 	 | Rows  | Bytes | Cost (%CPU)| Time	 |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		 |    10 |    50 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY	 |		 |	 |	 |	      | 	 |
|   2 |   INDEX FULL SCAN| IND_OBJECT_ID |    10 |    50 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<11)
Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  3  consistent gets
	  0  physical reads
	  0  redo size
	645  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 10  rows processed
SQL> select /*+ index_ffs(t,ind_object_id) */ object_id from t where rownum<11  order by object_id;
 OBJECT_ID
----------
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10
	11
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4250658493
------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name	       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	       |    10 |    50 |       |   296	 (2)| 00:00:04 |
|   1 |  SORT ORDER BY	       |	       |    10 |    50 |   864K|   296	 (2)| 00:00:04 |
|*  2 |   COUNT STOPKEY        |	       |       |       |       |	    |	       |
|   3 |    INDEX FAST FULL SCAN| IND_OBJECT_ID | 72526 |   354K|       |    72	 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<11)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  8  consistent gets
	  0  physical reads
	  0  redo size
	645  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	 10  rows processed
SQL> select object_id from t where rownum<11  order by object_id;
 OBJECT_ID
----------
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10
	11
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2312333252
----------------------------------------------------------------------------------
| Id  | Operation	 | Name 	 | Rows  | Bytes | Cost (%CPU)| Time	 |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		 |    10 |    50 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY	 |		 |	 |	 |	      | 	 |
|   2 |   INDEX FULL SCAN| IND_OBJECT_ID | 72526 |   354K|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<11)
Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  3  consistent gets
	  0  physical reads
	  0  redo size
	645  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 10  rows processed

INDEX FAST FULL SCAN需要排序，而INDEX FULL SCAN可以消除某些查询的排序操作

INDEX FULL SCAN会排序单块从左开始读取所有的叶节点，由于顺序读取的数据是有序的，所以可以消除查询的排序操作。而INDEX FAST FULL SCAN是多块读取全部索引的数据块，读取时是按照块的连续性，以extent为单位来读取（如同进行全表扫描一样），所以获取的数据是无序的，需要排序。db_file_multiblock_read_count用来设置多块读的块数。