ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 复合索引顺序选择问题(一)

复合索引顺序选择问题(一)

原创 Linux操作系统 作者:realkid4 时间:2011-04-12 22:24:24 0 删除 编辑

 

索引是我们经常选择的数据表检索优化方案之一。其中,复合索引是我们经常选择的策略。那么,构建索引列的顺序上,有何种差异和需要注意的方面呢?下面我们通过实验来进行说明。

 

实验环境说明

 

准备数据表和实验环境。索引列的差异,主要体现在选择性上,我们通过构建不同选择性的列来进行试验。

 

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> create table t as select owner, object_name from dba_objects;

 

Table created

 

SQL> select count(distinct owner), count(distinct object_name) from t;

 

COUNT(DISTINCTOWNER)  COUNT(DISTINCTOBJECT_NAME)

-------------------- --------------------------

                  30                      30716

 

 

可以看出,在数据表T上不同列具有很大的选择性差异。

 

 

构建方案1——低选择性列为前导列

 

首先我们选择低选择性列owner作为索引列的前导列。

 

 

SQL> create index idx_t_cmp1 on t(owner,object_name);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

首先来观察一下索引物理体积问题。

 

 

SQL> col segment_name for a15;

SQL> select segment_name, bytes, blocks, extents from user_segments where segment_name='IDX_T_CMP1';

 

SEGMENT_NAME         BYTES     BLOCKS    EXTENTS

--------------- ---------- ---------- ----------

IDX_T_CMP1         3145728        384         18

 

 

占有空间上为384Oracle块,分布在18个分区上。

 

 

搜索场景执行计划研究。

 

场景1where条件中包括所有索引列;

 

 

SQL> explain plan for select * from t where wner='SCOTT' and object_name='T';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1474811917

-------------------------------------------------------------------------------

| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |            |     1 |    29 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T_CMP1 |     1 |    29 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='T')

 

13 rows selected

 

 

 

当所有列均出现在where条件中时,Oracle选择的执行计划中进行“INDEX RANGE SCAN”操作。Oracle索引结构中,叶节点排列的就是索引列排序的结果。进行的“INDEX RANGE SCAN”操作,就是首先根据条件,从根root节点位置向下定位,经过分支节点之后,定位到第一个符合条件索引列键值的叶节点。之后顺序扫描叶子节点,获取到符合where条件(或者部分where符合条件)的数据表列rowid值。

 

Index Range Scan操作是Oracle进行索引操作最常见的形式。

 

 

场景2where中包括低选择性列

 

 

SQL> explain plan for select * from t where wner='SCOTT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1474811917

-------------------------------------------------------------------------------

| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |            |  1901 | 55129 |    12   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T_CMP1 |  1901 | 55129 |    12   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("OWNER"='SCOTT')

 

13 rows selected

 

 

当条件中只有低选择性列的时候,Oracle同样可以通过INDEX RANGE SCAN来获取rowid值。虽然并不能完全发挥出索引的全部列优势,但是Oracle通过Cost试算,通常可以判断出只扫描部分索引树,也是能带来较好的搜索性能的。

 

 

场景2where条件中带高选择性列

 

 

SQL> explain plan for select * from t where object_name='T';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3522166362

-------------------------------------------------------------------------------

| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |            |     2 |    58 |    28   (0)| 00:00:01 |

|*  1 |  INDEX SKIP SCAN | IDX_T_CMP1 |     2 |    58 |    28   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("OBJECT_NAME"='T')

       filter("OBJECT_NAME"='T')

 

14 rows selected

 

 

此处,where条件中没有出现索引前导列owner,而是出现了选择性较强的object_name列。此时,我们发现Oracle选择利用索引进行了“INDEX SKIP SCAN”操作。首先,我们从CBO的角度看,进行该操作所消耗的成本必然要比进行FTS(全表扫描)的成本要低。

 

INDEX SKIP SCANOracle 9i中引入的一种执行计划操作。故名思意,就是对索引叶节点进行“跳跃”式的搜索。在这个问题上,网络中一些资料认为:

 

Oracle中的复合索引顺序不同,对索引构建结构上有很大的影响。首先,Oracle依据前导列的取值将索引树划分为多个子索引结构。如果前导列取值较多,也就意味着子树多。在进行带前导列搜索时,Oracle首先依据前导列确定子索引树,之后进行各种的Index Range Scan。此时的Range Scan是进行索引叶子节点的扫描。

 

无论这种理解是否正确,有一点可以肯定。当where条件中不包括前导列的时候,对叶子节点进行Range Scan应该是不可以的。因为Range Scan保证的顺序是前导列+后导列的顺序。Skip Scan应该进行的是在叶子节点上,根据不同的前导列形成子索引树,叶节点分别进行Scan操作。

 

 

笔者以为:skip scanOracle针对特定条件上索引结构,所提供的一种备选搜索操作。Skip scan的使用不是规则,而是成本估算。Index Skip ScanOracle提供的一种执行计划操作,可以应用在执行计划的生成中。简单的说,就是OracleSQL描述语句转化为可执行操作序列(执行计划)过程中一个操作选择。

 

 

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

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

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7676719