索引Index的出现，对Oracle优化器而言意味着更多的路径选择。随着CBO的不断智能化，一些借助索引特点出现执行计划更好的提高SQL的执行效果。
Index Full Scan和Index Fast Full Scan是两个Oracle索引执行计划中常常出现的Access Path。两者存在一些相同点，也有一些差异。
常见的相同点有：
ü 两者都是针对索引段的执行计划，而且访问焦点的是索引叶子节点上的索引值。两种访问方式Oracle都不需要进行“回表”操作，操作过程不会涉及到用rowid列表找数据段的过程；
ü 更加智能化。即使where条件中不包括索引列条件，两种访问方式同样会出现；
两者的差异在于：
ü Index Full Scan返回的结果集合是有序的，即使在SQL语句中没有对应的order by字句。而Index Fast Full Scan的结果集合是不保证有序；
ü Index Full Scan不支持并行操作。而Index Fast Full Scan是支持并行操作的。
在实际使用中，我们可以看到两者的场景差异。数据集合较小的Index操作，往往会选择Index Full Scan这种比较传统的动作。而当数据集合较大，索引段较大的时候，Index Fast Full Scan则是CBO的优先选择。
从实际效果看，两种访问方式都是针对所有索引叶子节点的扫描操作，进行诸如计数或者列举动作。那么，在执行动作方面，两个Access Path是有什么差别呢？本篇来探讨一下。
1、环境介绍
本文使用Oracle 11gR2进行试验，创建实验数据表和收集统计量。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
为了进行试验，我们选择创建两个数据表。两个结构相同但是数据量存在差异。首先是小数据表。
--小数据表
SQL> create table t_small as select * from dba_objects where rownum<10;
Table created
--非空列设置
SQL> alter table t_small modify object_id not null;
Table altered
--索引结构
SQL> create index idx_t_small_id on t_small(object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T_SMALL',cascade => true);
PL/SQL procedure successfully completed
数据表t_small只有包括9条数据。段分配信息和数据块信息如下：
SQL> select segment_name, blocks, extents from dba_segments where wner='SYS' and segment_name in ('T_SMALL','IDX_T_SMALL_ID');
SEGMENT_NAME BLOCKS EXTENTS
--------------- ---------- ----------
T_SMALL 8 1
IDX_T_SMALL_ID 8 1
SQL> select num_rows, blocks from dba_tables where wner='SYS' and table_name='T_SMALL';
NUM_ROWS BLOCKS
---------- ----------
9 1
SQL> select BLEVEL, LEAF_BLOCKS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from dba_indexes where wner='SYS' and index_name = 'IDX_T_SMALL_ID';
BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY NUM_ROWS
---------- ----------- ----------------------- ----------------------- ----------
0 1 1 1 9
数据表t_small高水位线（HWM）下只有一个数据块。对应索引idx_t_small_id包括的叶子块也只有一个。T_small是一个很小的数据表。
下面创建大表。
SQL> create table t_big as select * from dba_objects;
Table created
SQL> alter table t_big modify object_id not null;
Table altered
SQL> create index idx_t_big_id on t_big(object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T_BIG',cascade => true);
PL/SQL procedure successfully completed
对应的存储信息为：
SQL> select segment_name, blocks, extents from dba_segments where wner='SYS' and segment_name in ('T_BIG','IDX_T_BIG_ID');
SEGMENT_NAME BLOCKS EXTENTS
--------------- ---------- ----------
T_BIG 1152 24
IDX_T_BIG_ID 256 17
SQL> select num_rows, blocks from dba_tables where wner='SYS' and table_name='T_BIG';
NUM_ROWS BLOCKS
---------- ----------
72689 1034
SQL> select BLEVEL, LEAF_BLOCKS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from dba_indexes where wner='SYS' and index_name = 'IDX_T_BIG_ID';
BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY NUM_ROWS
---------- ----------- ----------------------- ----------------------- ----------
1 161 1 1 72689
注意：t_big数据表高水位线下1034个数据块，分布在24个extent上。索引idx_t_big对应17个分区，叶子块有161个（注意这个数字）。
2、常规执行计划分析
我们首先看一下CBO对于大小两个数据表在相同SQL结构下的不同选择。
SQL> explain plan for select count(*) from t_small;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1767817138
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_T_SMALL_ID | 9 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
9 rows selected
我们的SQL中没有where条件，也没有明确的指定索引路径hint，CBO依然选择了索引路径。因为，我们明确指定了object_id列为非空，并且在其上构建了索引。
对CBO而言，有至少两条备选路径可以选择。其一是传统的FTS（Full Table Scan），从数据表段头开始扫描所有数据块，直到HWM。另一条是借助索引的“蹊径”。
在得到object_id列非空的前提下，CBO认为索引树idx_t_small_id叶子节点的数目实际上就是数据表行数。对索引段读取的I/O量明显要小于数据表段，而且不需要“回表”操作。于是，CBO选择单独读取索引结构进行计数，也就是Index Full Scan。
那么，对数据量增加的t_big，事情是如何呢？
SQL> explain plan for select count(*) from t_big;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2892922722
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_BIG_ID | 72689 | 43 (0)| 00:00:01 |
------------------------------------------------------------------------------
9 rows selected
相同的环境，不同的数据量，CBO做出了不同的路径选择。在t_big的计数操作中，Oracle选择了Index Fast Full Scan路径。但是，Index Fast Full Scan的效果和Index Full Scan的结果是一样，都是对索引段节点计数。
从实际情况来看，CBO在两个Access Path的选择过程中遵守了这样的原则：如果数据表很小，Index Full Scan方式更加容易被选择到。反之，Index Fast Full Scan更加容易出现。
当然，我们可以通过hint指定的方法，强令Oracle选择路径。
SQL> explain plan for select /*+index(t_big)*/count(*) from t_big;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2587926039
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 162 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_T_BIG_ID | 72689 | 162 (0)| 00:00:03 |
-------------------------------------------------------------------------
9 rows selected
通过hint控制，我们也可以让t_big的操作走index full scan，但是我们要注意到，成本值为162，远远高于index fast full scan的43。
那么，index fast full scan和index full scan在行为上的差异是什么呢？两种操作必然有独特的特点，让CBO在进行评估时候有不同的取舍。下面，我们将使用10046对两种操作进行更加细致的分析。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-751979/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-751979/
235
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
