Pro ORACLE SQL一书错误之处

最新推荐文章于 2024-10-19 12:43:06 发布

最新推荐文章于 2024-10-19 12:43:06 发布 · 91 阅读

文章标签：

本文通过两个示例对比了IndexFastFullScan与INDEXFULLSCAN的区别。当列设置为NOT NULL时，选择了IndexFastFullScan，这能提高查询效率；反之，则进行全表扫描。文章还解释了IndexFastFullScan为何不能避免排序。

书中87页

作者讲解 Index Fast Full Scan

引用了2个例子

一个是列允许为null,走了全表扫描

一个是列不允许为null,走的是INDEX FULL SCAN，我就纳闷了，这里明明讲的是Index Fast Full Scan结果例子是INDEX FULL SCAN
Index Fast Full Scan
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.
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.
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.