INDEX FULL SCAN和INDEX FAST FULL SCAN區別

---請記住這個INDEX FULL SCAN掃描方式，並體會與INDEX FAST FULL SCAN的區別


drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000


select * from t  order by object_id;
執行計劃
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 88780 |    17M|  1208   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             | 88780 |    17M|  1208   (1)| 00:00:15 |
|   2 |   INDEX FULL SCAN           | IDX_OBJECT_ID | 88780 |       |   164   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10873  consistent gets
          0  physical reads
          0  redo size
    8116181  bytes sent via SQL*Net to client
      54040  bytes received via SQL*Net from client
       4877  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73130  rows processed


drop table t purge;
create table t as select * from dba_objects ;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000


select count(*) from t;
執行計劃
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    49   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 88780 |    49   (0)| 00:00:01 |
-------------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        170  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

總結：fast為無序讀，一次讀取多個塊，full為有序讀，每次讀取單個塊。
梁老師課程總結。