一、说明
1、IFS （index full scan） 单块读，IFFS(index fast full scan)多块读。
2、在同时对表中某一列进行全扫描的时候看，多块读的速度明显要比单块读要快，性能要更好。
3、FTS(full table scan)和IFFS(index fast full scan）都为多块读。
4、IFFS(index fast full scan)为多块读，可并行，非排序
5、IFS(index full scan)为单块读、有序。
二、测试过程
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.30
SQL> select /*+ index(tt idx_object_id) */ count(object_id) from tt;
COUNT(OBJECT_ID)
—————-
5524288
Elapsed: 00:00:05.72
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.07
SQL> select count(object_id) from tt;
COUNT(OBJECT_ID)
—————-
5524288
Elapsed: 00:00:01.35
SQL> explain plan for select /*+ index(tt idx_object_id) */ count(object_id) from tt;
Explained.
Elapsed: 00:00:00.07
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
————————————————————————————————————————
Plan hash value: 3277332215
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 5 | 12269 (1)| 00:02:28 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN| IDX_OBJECT_ID | 2762K| 13M| 12269 (1)| 00:02:28 |
———————————————————————————-
9 rows selected.
Elapsed: 00:00:00.33
SQL> explain plan for select count(object_id) from tt;
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
————————————————————————————————————————
Plan hash value: 1131838604
—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 5 | 3335 (1)| 00:00:41 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 2762K| 13M| 3335 (1)| 00:00:41 |
—————————————————————————————
9 rows selected.
Elapsed: 00:00:00.01
SQL>
