INDEX FULL SCAN 索引全扫描。单块读 。它扫描的结果是有序的,因为索引是有序的。它通常发生在 下面几种情况(注意:即使SQL满足以下情况 不一定会走索引全扫描) 1. SQL语句有order by选项,并且order by 的列都包含 在索引中,并且order by 后列顺序必须和索引列顺序一致。 2. 在进行SORT MERGE JOIN的时候,如果要查询的列通过索 引就能获得,那就不必进行全表扫描了,另外也避免了排 序,因为INDEX FULL SCAN返回的结果已经排序。 3. 当查询中有GROUP BY,并且GROUP BY 的列包含在索引中。 等待事件:db file sequential read HINT: INDEX(表名/别名 索引名) INDEX FAST FULL SCAN 索引快速全扫描。多块读 。当SQL要查询的数据能够完全从索引中获得,那么 Oracle就不会走全表扫描了,就会走索引快速全 扫描。索引快速全扫描类似全表扫描,它可以多块 读,并且可以并行扫描。 等待事件:db file scattered read HINT:INDEX_FFS(表名/别名 索引名) 测试INDEX FAST FULL SCAN: create table test as select * from dba_objects; create index test_idx1 on test(object_name); BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR', tabname => 'TEST', estimate_percent => 30, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE); END; SQL> explain plan for select object_name from test; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1159K| 27M| 4687 (1)| 00:00:57 | | 1 | TABLE ACCESS FULL| TEST | 1159K| 27M| 4687 (1)| 00:00:57 | -------------------------------------------------------------------------- 8 rows selected. --从上面的执行计划中可知,此时走了全表扫描。 --由于我们需要查询的列为object_name,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢? --这是因为NULL值与索引的特性所决定的。即null值不会被存储到B树索引。因此应该为表 test 的列 object_name 添加 not null 约束,或者 where条件里加上is not null 此例中虽然SQL> select count(*) from test where object_name is null; COUNT(*) ---------- 0 object_name 不存在空值------------------- SQL> set autot trace exp SQL> select object_name from test where object_name is not null; Execution Plan ---------------------------------------------------------- Plan hash value: 703934364 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1161K| 27M| 1563 (1)| 00:00:19 | |* 1 | INDEX FAST FULL SCAN| TEST_IDX1 | 1161K| 27M| 1563 (1)| 00:00:19 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME" IS NOT NULL) ---INDEX FULL SCAM SQL> select object_name from test order by object_name; Execution Plan ---------------------------------------------------------- Plan hash value: 2007178810 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1161K| 27M| | 13060 (1)| 00:02:37 | | 1 | SORT ORDER BY | | 1161K| 27M| 35M| 13060 (1)| 00:02:37 | | 2 | TABLE ACCESS FULL| TEST | 1161K| 27M| | 4687 (1)| 00:00:57 | ----------------------------------------------------------------------------------- SQL> select object_name from test where object_name is not null order by object_name; Execution Plan ---------------------------------------------------------- Plan hash value: 436181854 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1161K| 27M| 5754 (1)| 00:01:10 | |* 1 | INDEX FULL SCAN | TEST_IDX1 | 1161K| 27M| 5754 (1)| 00:01:10 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME" IS NOT NULL)