一、说明

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;

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

Elapsed: 00:00:00.07

sql> select count(object_id) from tt;

Elapsed: 00:00:01.35

sql> explain plan for select /*+ index(tt idx_object_id) */ count(object_id) from tt;

Explained.

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;

Elapsed: 00:00:00.01

Plan hash value: 1131838604

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 3335 (1)| 00:00:41 |

| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 2762K| 13M| 3335 (1)| 00:00:41 |

sql>