INDEX FULL SCAN和INDEX FAST FULL SCAN的区别

关于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 <= 10000
  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代价小了很多。

所以证明了我所说的。

转载于:https://my.oschina.net/sniperLi/blog/357251