Oracle 11g IFS VS IFFS 性能对比
时间:2022-03-15 07:36
一、说明
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>