Index Full Scan vs Index Fast Full Scan
  index full scan和index fast full scan是指同样的东西吗?答案是no。两者虽然从字面上看起来差不多,但是实现的机制完全不同。我们一起来看看两者的区别在哪里?
  
  首先来看一下IFS,FFS能用在哪里:在一句sql中,如果我们想搜索的列都包含在索引里面的话,那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句:
  
  SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
  
  SQL> CREATE INDEX ind_test_id ON TEST(object_id);
  
  SQL> INSERT INTO TEST
  SELECT  *
  FROM dba_objects
  WHERE object_id IS NOT NULL AND object_id > 10000
  ORDER BY object_id DESC;
  
  17837 rows created.
  
  SQL> analyze table test compute statistics for table for all columns for all indexes;
  
  Table analyzed.
  SQL> set autotrace trace;
  
  SQL> select object_id from test;
  
  17837 rows selected.
  
  Execution Plan
  ----------------------------------------------------------
    0   SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
    1  0  TABLE Access (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
  
  这时候 Oracle会选择全表扫描,因为 object_id 列默认是可以为null的,来修改成 not null:
  
  
  SQL>alter table test modify(object_id not null);
  
  SQL> select object_id from test;
  
  17837 rows selected.
  
  Execution Plan
  ----------------------------------------------------------
    0   SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
    1  0  INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)
  
  当然我们也可以使用index full scan:
  
  
  SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test;
  
  17837 rows selected.
  
  Execution Plan
  ----------------------------------------------------------
    0   SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
    1  0  INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)
  
  我们看到了两者都可以在这种情况下使用,那么他们有什么区别呢?有个地方可以看出两者的区别, 来看一下两者的输出结果,为了让大家看清楚一点,我们只取10行。
  
  INDEX FAST FULL SCAN
  
  SQL> select object_id from test where rownum<11;
  
   OBJECT_ID
  ----------
     66266
     66267
     66268
     66269
     66270
     66271
     66272
     66273
     66274
     66275
  10 rows selected.
  
  
  INDEX FULL SCAN
  
  SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<11;
  
   OBJECT_ID
  ----------
     10616
     12177
     12178
     12179
     12301
     13495
     13536
     13539
     13923
     16503
  10 rows selected.
  
  可以看到两者的结果完全不一样,这是为什么呢?这是因为当进行index full scan的时候 oracle定位到索引的root block,然后到branch block(如果有的话),再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。