《Pro Oracle SQL》Chapter3--3.2.7 Index Fast Full Scan

Index Fast Full Scan  索引快速全扫描    (page 109)
    An index fast full scan is more like a full table scan than like other index scan types.  When an index
fast full scan operation is chosen, all the index blocks are read using multiblock reads.  This type of
scan is chosen as an alternative to a full table scan when all the columns needed to satisfy the query’s
column list are included in the index and at least one column in the index has the  NOT NULL constraint.  
In this case, the data is accessed from the index instead of having to access table blocks.  Unlike other
index scan types, the index fast full scan cannot be used to avoid a sort since the blocks are read using
unordered multiblock reads.   Listing 3-16 shows an example of an index fast full scan plan.
    索引快速全扫描相比其他的索引扫描类型更像全表扫描。当索引快速全扫描被选择，则所有的索引块使用多块读读取。当查询列集中的所有列 都被包含在索引中且至少索引中的一列有NOT NULL约束，则会选择这种类型的扫描作为全表扫描的替代。 这种情况下，数据通过索引访问而不是必须访问表块。不像其他的索引扫描类型，索引快速全扫描不能用于避免排序，因为块是用无序的多块读读取的。 列表3-16展示了一个索引快速全扫描计划的例子。
Listing 3-16. Index Fast Full Scan  
SQL> alter table hr.employees modify (email null) ;
 
Table altered.
 
SQL> set autotrace traceonly explain
SQL> select email from hr.employees ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
 
--------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |   107   |   856  |     3   (0)    |
|   1 |  TABLE ACCESS FULL | EMPLOYEES |   107   |   856  |     3   (0)    |
--------------------------------------------------------------------
 
SQL> set autotrace off
SQL>
SQL> alter table hr.employees modify (email not null) ;
 
Table altered.
 --HR    EMP_EMAIL_UK    Unique    EMAIL 
SQL> set autotrace traceonly explain
SQL> select email from hr.employees ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
 
----------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |   107   |   856  |     1   (0)      |
|   1 |  INDEX FULL SCAN      | EMP_EMAIL_UK |   107   |   856  |     1   (0)       |
----------------------------------------------------------------------
 
    This example demonstrates how the index fast full scan operation relies on the  NOT NULL
constraint in order to be chosen.  Without the constraint, a full scan operation is chosen instead.
    本例示例了索引快速全扫描如何依赖于NOT NULL约束而被选中。没有这个约束，就会选中全表扫描操作。

 

注：这里计划是INDEX FULL SCAN而不是i ndex fast full scan

参看如下链接

http://blog.csdn.net/robinson1988/article/details/6173974

先发现这个错误。