一、说明
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>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
