INDEX FULL SCAN和INDEX FAST FULL SCAN的区别
时间:2016-05-29 02:42 来源:linux.it.net.cn 作者:IT
关于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代价小了很多。
所以证明了我所说的。
(责任编辑：IT)
| 关于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代价小了很多。 所以证明了我所说的。 (责任编辑：IT) |