一、說明

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供泰州網(wǎng)站建設(shè)、泰州做網(wǎng)站、泰州網(wǎng)站設(shè)計(jì)、泰州網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、泰州企業(yè)網(wǎng)站模板建站服務(wù)，10多年泰州做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

1、IFS （index full scan） 單塊讀，IFFS(index fast full scan)多塊讀。

2、在同時(shí)對(duì)表中某一列進(jìn)行全掃描的時(shí)候看，多塊讀的速度明顯要比單塊讀要快，性能要更好。

3、FTS(full table scan)和IFFS(index fast full scan）都為多塊讀。

4、IFFS(index fast full scan)為多塊讀，可并行，非排序

5、IFS(index full scan)為單塊讀、有序。

二、測(cè)試過程

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>