Oracle 11g IFS VS IFFS 性能对比

一、说明

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>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!