原创 转载请注明出处
（原创）
关于INDEX FULL SCAN和INDEX FAST FULL SCAN的区别在于，前者在对索引进行扫描的时候会考虑大索引的结构，而且会按照索引的排序，
而后者则不会，INDEX FAST FULL SCAN不会去扫描根块和分支块，对索引像访问堆表一样访问，所以这两个扫描方式用在不同的场合
如果存在ORDER BY这样的排序，INDEX FULL SCAN是合适的，如果不需要排序，那INDEX FAST FULL SCAN效率是更高的。
试验如下：
SQL> drop table t;
Table dropped
SQL>
SQL> CREATE TABLE t (
2 id NUMBER,
3 n1 NUMBER,
4 n2 NUMBER,
5 pad VARCHAR2(4000),
6 CONSTRAINT t_pk PRIMARY KEY (id)
7 );
Table created
SQL> execute dbms_random.seed(0)
PL/SQL procedure successfully completed
SQL> INSERT INTO t
2 SELECT rownum AS id,
3 1+mod(rownum,251) AS n1,
4 1+mod(rownum,251) AS n2,
5 dbms_random.string('p',255) AS pad
6 FROM dual
7 CONNECT BY level 8 ORDER BY dbms_random.value;
10000 rows inserted
SQL> CREATE INDEX t_n1_i ON t (n1);
Index created
SQL>
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 'T',
5 estimate_percent => 100,
6 method_opt => 'for all columns size skewonly',
7 cascade => TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed
建立试验环境完成
一、语句需要排序的情况
1、使用INDEX FULL SCAN
SQL> explain plan for
2 SELECT /*+ index(t t_n1_i) gather_plan_statistics */
3 n1
4 FROM t
5 WHERE n1 IS NOT NULL
6 ORDER BY n1;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1041622781
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 27 (19)| 00:00:01 |
|* 1 | INDEX FULL SCAN | T_N1_I | 10000 | 40000 | 27 (19)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1" IS NOT NULL)
13 rows selected
可以看到这里执行计划并没有SORT出现
2、强制使用INDEX FAST FULL SCAN
SQL> explain plan for
2 SELECT /*+ index_ffs(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL order by n1;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3958789139
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | | 45 (36)| 00
| 1 | SORT ORDER BY | | 10000 | 40000 | 248K| 45 (36)| 00
|* 2 | INDEX FAST FULL SCAN| T_N1_I | 10000 | 40000 | | 7 (43)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1" IS NOT NULL)
14 rows selected
可以清楚的看到这里出现SORT，大量的COST出现在SORT这里，
所以排序的情况INDEX FULL SCAN优于INDEX FAST FULL SCAN。
二、没有排序的情况
我们只需要去掉最后ORDER BY 就OK了
1、使用INDEX FULL SCAN
SQL> explain plan for
2 SELECT /*+ index(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1041622781
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 27 (19)| 00:00:01 |
|* 1 | INDEX FULL SCAN | T_N1_I | 10000 | 40000 | 27 (19)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1" IS NOT NULL)
13 rows selected
可以看到这里根本没有变化，所以排序与否（ASC,如果是DESC会稍有变化）对执行计划没有影响
2、使用INDEX FAST FULL SCAN
SQL> explain plan for
2 SELECT /*+ index_ffs(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 263832501
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (43)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| T_N1_I | 10000 | 40000 | 7 (43)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1" IS NOT NULL)
13 rows selected
没有出现SORT代价小了很多。
所以证明了我所说的。