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用来设置多块读的块数。