|
关于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) |