Oracle11gIFSVSIFFS性能对比
一、说明
成都创新互联专业为企业提供泰州网站建设、泰州做网站、泰州网站设计、泰州网站制作等企业网站建设、网页设计与制作、泰州企业网站模板建站服务，10多年泰州做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
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>
网页题目：Oracle11gIFSVSIFFS性能对比
分享路径：http://cdiso.cn/article/gojcdh.html
