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>