组合索引在应对特殊的多条件查询是，可以起到意想不到的效果。我们经常在网络上讨论组合索引顺序选取的原则。

江湖流传原则多种多样，有建议使用选择率大小判断的，有建议查看分布的，更有追求多需求共用的。这些方案都有一定的道理和支持成功案例。笔者以为，组合索引顺序问题，还要具体情况具体分析。最重要的因素，还是我们的SQL访问方式。

1、  问题提出

最近接触到一个报表需求，要求从文件加载日志中出具报表数据。条件特征是一个等于条件和一个日期范围条件。由于日志数据表是一个累积性的数据表，所以潜在数据量可能是很大的。下面是对实验环境的模拟。

SQL> create table t as select * from dba_objects;

Table created

SQL语句：select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

获取owner为ctxsys的在2009年9月的全部数据。

2、  无索引情况

作为参照，我们首先查看一下无索引方案的时候，Oracle执行计划和相应的消耗。说明，在每次SQL语句前，为了从实验条件公平，都将shared_pool和buffer_Cache进行清空。

SQL> select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

已选择105行。

执行计划

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |    99 |  9603 |   282   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |    99 |  9603 |   282   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OWNER"='CTXSYS' AND "CREATED"<=TO_DATE(' 2009-09-30

              23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "CREATED">=TO_DATE('

              2009-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

统计信息

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

        577  recursive calls

          0  db block gets

       1156  consistent gets

       1039  physical reads

          0  redo size

       6190  bytes sent via SQL*Net to client

        442  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

         17  sorts (memory)

          0  sorts (disk)

        105  rows processed

在没有任何优化方案的情况下，Oracle选择了全表扫描作为执行路径。

3、  组合索引方案——方案1

如果要对该数据SQL进行优化，注意我这里说的是如果。一个系统中，SQL形态需求多样，哪个进行优化，哪个放任不管都是有所选取的。并不是每个需求我们都要进行优化，优化是有代价和副产品的，我们要将有限的资源投入到关键用例需求的优化中。RUP中有句名言：关键用例决定架构，同样道理，关键用例决定优化方向。

我们计划选择组合索引，索引列为owner和created两个列。那么，就出现哪个作为前导列的问题。我们首先选择created作为前导列。

SQL> create index idx_t_comp1 on t(created,owner);

Index created

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

PL/SQL procedure successfully completed

SQL> select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

已选择105行。

执行计划

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

Plan hash value: 3419152522

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

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

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

|   0 | SELECT STATEMENT            |             |   160 | 15520 |   238   (0)| 00:00:03 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   160 | 15520 |   238   (0)| 00:00:03 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_COMP1 |   160 |       |    78   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("CREATED">=TO_DATE(' 2009-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

              AND "OWNER"='CTXSYS' AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

filter("OWNER"='CTXSYS')

统计信息

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

        621  recursive calls

          0  db block gets

        302  consistent gets

         93  physical reads

          0  redo size

       8556  bytes sent via SQL*Net to client

        442  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

         18  sorts (memory)

          0  sorts (disk)

        105  rows processed

在CBO的作用下，Oracle选择了索引路径。相应的成本显而易见。下面我们看看替代方案。

4、  组合索引方案——方案2

这次我们选择owner、created顺序建立组合索引。

SQL> create index idx_t_comp2 on t(owner,created);

Index created

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

PL/SQL procedure successfully completed

SQL>  select * from t where created between to_date('2009-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2009-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and wner='CTXSYS';

已选择105行。

执行计划

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

Plan hash value: 2902496991

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

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

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

|   0 | SELECT STATEMENT            |             |    89 |  8633 |    90   (0)| 00:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    89 |  8633 |    90   (0)| 00:00:02 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_COMP2 |    89 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='CTXSYS' AND "CREATED">=TO_DATE(' 2009-09-01 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

统计信息

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

        621  recursive calls

          0  db block gets

        228  consistent gets

         19  physical reads

          0  redo size

       8556  bytes sent via SQL*Net to client

        442  bytes received via SQL*Net from client

          8  SQL*Net roundtrips to/from client

         18  sorts (memory)

          0  sorts (disk)

        105  rows processed

下面我们对比三种执行路径的成本情况。

优劣显而易见，组合顺序owner、created具有绝对的优势地位。那么，为什么数据集合相同，SQL语句相同，而且都是选择组合索引，只是顺序的不同带来成本如此大的变化呢？

5、  原因分析

这个原因应该从组合索引结构和不同结构下SQL的搜索行为入手分析。

我们先来看第一个组合索引方案（created owner）。根据Oracle索引树的结构，索引列会全部出现在索引树的叶子节点上。不同的前导列，差距就是叶子节点上排序的顺序，会影响到Oracle检索的数据量。当我们选择created owner顺序时，Oracle叶子节点排序的顺序就是有限进行Created排序、之后进行owner排序，形成下面图的结构。

created列在前，owner列在后。在进行检索的时候，Oracle为了满足第一个时间范围条件，会将所有的阴影部分叶子节点都扫过一遍，将阴影索引块全部复制在buffer cache中。这时候，我们注意到其中有owner非ctxsys的数据行，就会将这些记录再次进行剔除选择。

这个过程我们可以从方案一的执行计划中看出。

   2 - access("CREATED">=TO_DATE(' 2009-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

              AND "OWNER"='CTXSYS' AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

filter("OWNER"='CTXSYS')

注意，此处Oracle是先按照时间范围进行access获取操作，之后再按照owner条件进行filter操作。这是两步操作。

再回头看我们的索引方案二。索引结构不同，引起的扫描方式也就是不同的。

首先，Oracle从分支branch节点定位到第一条owner为ctxsys的叶子节点上。之后顺序进行scan，应用created日期范围条件。这样，只要通过scan操作就可以取到所有的结果集合，不需要向方案一一样还要进行删选选择。我们的猜想可以通过执行计划进行证实。

   2 - access("OWNER"='CTXSYS' AND "CREATED">=TO_DATE(' 2009-09-01 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2009-09-30 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

可以看到，这个执行计划中，只有access操作，而且是充分应用上了全部条件。这样做，访问索引块的个数、进行CPU消耗的量就会小很多。从而，执行计划就更加优化。

6、结论

通过这个案例，笔者主要想说明几个问题：

ü        我们在确定确实需要进行组合索引优化方案的时候，要关注前导列的选取问题，绝对不要轻易相信各种“流言秘籍”，多问问为什么；

ü        不同的前导列选取方案，选择的时候要看清楚访问SQL的组合条件究竟是什么方式？范围条件和等于条件很大程度上会影响到我们的方案；

ü        最后确定的前导列方案，是一个综合全局的方案，还要考虑其他功能对该表的访问方式。本篇中我们没有涉及到对该表其他关键优化需求。一切优化方案所追求的就是一系列平衡，性能的平衡、用例功能间的平衡、各种成本的平衡乃至收益付出的平衡；

优化方案选取就是一个选取寻找平衡的过程。

indexcomposit1.jpg

indexcomposit2.jpg