书中87页
作者讲解 Index Fast Full Scan
引用了2个例子
一个是列允许为null,走了全表扫描
一个是列不允许为null,走的是INDEX FULL SCAN，我就纳闷了，这里明明讲的是Index Fast Full Scan结果例子是INDEX FULL SCANIndex Fast Full ScanAn index fast full scan is more like a full table scan than like other index scan types. When an indexfast full scan operation is chosen, all the index blocks are read using multiblock reads. This type ofscan is chosen as an alternative to a full table scan when all the columns needed to satisfy the query’scolumn 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 otherindex scan types, the index fast full scan cannot be used to avoid a sort since the blocks are read usingunordered multiblock reads. Listing 3-16 shows an example of an index fast full scan plan.Listing 3-16. Index Fast Full ScanSQL> alter table hr.employees modify (email null) ;Table altered.SQL> set autotrace traceonly explainSQL> 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 offSQL>SQL> alter table hr.employees modify (email not null) ;Table altered.SQL> set autotrace traceonly explainSQL> 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 NULLconstraint in order to be chosen. Without the constraint, a full scan operation is chosen instead.
呵呵，虽然书中有错误，不过这本书确实是一本好书，大家有空可以读读
