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代價小了很多。

所以證明了我所說的。