复合索引顺序选择问题（二）

 

下面我们转换一种构建策略，使用高选择性列为前导列

 

 

构建方案2——高选择性列为前导列

 

构建索引

 

 

SQL> drop index idx_t_cmp1;

 

Index dropped

 

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

 

Index created

 

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

 

PL/SQL procedure successfully completed

 

 

 

先查看一下索引体积情况。

 

 

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

 

SEGMENT_NAME         BYTES     BLOCKS    EXTENTS

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

IDX_T_CMP2         3145728        384         18

 

 

对比之前的统计情况，我们发现体积没有变化。使用不同的索引列顺序，是不会影响到索引体积的。

 

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

 

 

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: 1025415826

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

 

13 rows selected

 

 

当索引列均出现在where中的时候，没有差别。都是使用Index range scan的执行计划路径。

 

 

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

 

 

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

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |    28 |   812 |    61   (4)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |    28 |   812 |    61   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

 

13 rows selected

 

 

差异在此处出现了。当where条件中出现的非前导列的时候，Oracle没有进行刚刚的index skip scan操作，而是选择full table scan执行操作。如果进行skip scan的条件是正确的话，说明当前导列的选择性较强，进行跳跃次数比较多的时候，Oracle会认为这种方式成本cost过高。退而选择FTS来进行操作。

 

 

可见，当选择高选择性列作为索引前导列的时候，where条件出现单后导列条件时，Oracle会倾向于走FTS。

 

 

 

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

 

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1025415826

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

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

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

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

|*  1 |  INDEX RANGE SCAN| IDX_T_CMP2 |     2 |    58 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

 

13 rows selected

 

 

单前导列时候进行Range Scan的情况，没有发生变化。

 

总结，经过上述的实验，我们已经初步了解了索引列顺序对组合索引的重要影响。

 

首先，索引列的选择性差异是引起索引顺序的根源所在。不同的索引列选择性集合从组合索引，一定要关注顺序问题，因为会严重影响到系统性能；

 

 

对于选择性，笔者不想直接宣称说一定要选择性如何如何的作为前导列。问题不同，系统的业务场景不同，使用不同的索引列顺序。作为我们，只要意识到不同顺序会给系统的性能和操作带来影响，进行调优的时候想得到调整索引列顺序是一个优化手段就可以了。

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

转载于:http://blog.itpub.net/17203031/viewspace-692445/