ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Index Full Scan和Index Fast Full Scan行为差异分析(上)

Index Full Scan和Index Fast Full Scan行为差异分析(上)

原创 Linux操作系统 作者:realkid4 时间:2013-01-01 18:22:26 0 删除 编辑

 

索引Index的出现,对Oracle优化器而言意味着更多的路径选择。随着CBO的不断智能化,一些借助索引特点出现执行计划更好的提高SQL的执行效果。

 

Index Full ScanIndex 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个数据块,分布在24extent上。索引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条件,也没有明确的指定索引路径hintCBO依然选择了索引路径。因为,我们明确指定了object_id列为非空,并且在其上构建了索引。

 

CBO而言,有至少两条备选路径可以选择。其一是传统的FTSFull 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 scan43

 

那么,index fast full scanindex full scan在行为上的差异是什么呢?两种操作必然有独特的特点,让CBO在进行评估时候有不同的取舍。下面,我们将使用10046对两种操作进行更加细致的分析。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-751979/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7822415