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;

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>