Oracle 11g IFS VS iffs Performance Comparison

I. Description

1, IFS (index full scan) single-block read, Iffs (index fast full scan) multi-block read.

2, at the same time in a table in a full scan of a column, the speed of more than a block of reading is significantly faster than a single block read, better performance.

3. The FTS (full table Scan) and Iffs (index fast full scan) are read multiple blocks.

4, Iffs (index fast full scan) for multi-block read, can be parallel, non-sorted

5, IFS (index full scan) is a single block read, orderly.

Second, the test process

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>

Oracle 11g IFS VS iffs Performance Comparison