新建的索引解决了问题，结论真的对吗？

今天这个案例相对比较简单，算是对基本原理的一个加强理解吧。

客户提供了一份其他公司做的优化报告，其中有个SQL确实是完成了优化，优化的结果也不错，但是给出的优化理由却是不准确的。

下面是报告对该SQL进行分析优化的描述：

SQL代码如下：

SELECT COUNT(1)

FROM CB_PROBLEM A

LEFT OUTER JOIN CB_PROJECT B ON A.PRJ_SEQ = B.PRJ_SEQ

LEFT OUTER JOIN BS_LINE C ON A.PRB_LINE = C.LINE_SEQ

LEFT OUTER JOIN BP_FLOW D ON A.PRB_FLOW = D.FLOW_SEQ

LEFT OUTER JOIN BP_NODE E ON A.PRB_NODE = E.NODE_SEQ

LEFT OUTER JOIN BS_EFFECT F ON A.EFFECT_LVL = F.VALUE_SEQ

LEFT OUTER JOIN BS_FREQUENCY G ON A.FREQUENCY_LVL = G.VALUE_SEQ

LEFT OUTER JOIN BS_S EVERITY H ON A.SEVERITY_LVL = H.VALUE_SEQ

WHERE A.STATUS != 0

AND A.PRB_ORG IN

(

SELECT ORG_ID

FROM SP_ORG

START WITH ORG_ID IN (:1, ......, :18)

CONNECT BY PRIOR ORG_ID = SUP_ORG

)

AND A.PRJ_SEQ IN

(

SELECT PRJ_SEQ

FROM CB_PROJECT

WHERE 1 = 1 AND PRJ_NAME LIKE '%xxxxxxx%'

);

执行计划如下（红字是老虎刘补注）：

（老虎刘注：看到这个执行计划有人可能会问，SQL语句中那么多做left join的表，上面为什么一个都没看到？

这是因为最后要的结果是count(1)，left join的表如果关联字段都是唯一的（这个是根据执行计划判断出来的），优化器会很聪明的把这些表从执行计划中消除，结果是等价的）。

其中主表CB_PROBLEM大小为312M，约44万行数据。SQL平均执行时间为6分钟。

CB_PROBLEM表当前索引情况：

INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION

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

ICIMS01 IDX_CB_PROBLEM_PRJ_SEQ PRJ_SEQ 1

ICIMS01 IDX_PROBLEM_ID PRB_ID 1

ICIMS01 IDX_PROBLEM_SEQ PRB_SEQ 1

ICIMS01 IDX_PROBLEM_SEQ PRJ_SEQ 2

ICIMS01 SYS_C006663 PRB_SEQ 1

（老虎刘注： IDX_PROBLEM_SEQ索引首字段与SYS_C006663重复，SYS_C006663索引可以删掉 ）

SQL中涉及的字段的选择性：

COLUMN_NAME NUM_DISTINCT

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

PRB_ORG 10319

PRJ_SEQ 75264

原报告由此得出的结论是：

可以看出，SQL中出现的字段PRJ_SEQ 已经在多个索引中出现，而且其选择性也不错， 为75264，之所以没走上该字段的索引，是由于该字段对应的子查询返回结果过多，达到1万多行，所以优化器没有其上的索引。

SQL中还涉及另一个字段PRB_ORG，它的选择性也不错，为10319，并且其上没有索引，经分析，建议在PRB_ORG和PRJ_SEQ上创建复合索引。

create index idx_test on ICIMS01.cb_problem(prb_org,prj_seq) online ;

我们先来看看增加索引后的SQL执行情况：

执行时间只需要2.73秒，比原来的6分钟有非常大的提高。执行计划如下：

下面我们来分析一下为什么说这个索引创建的理由是错误的。

执行计划中的步骤10由原来的全表扫描变成了索引扫描，这一步是创建索引的功劳。

执行计划中，SP_ORG表作为初始驱动表，cb_problem表是作为nested loops的被驱动表，与驱动表的关联字段只有一个：PRB_ORG，虽然使用的是两字段联合索引，但实际上只用到了第一个字段：PRB_ORG。

接下来cb_problem表又做为nested loops的驱动表，通过PRJ_SEQ字段驱动CB_PROJECT表时，使用的是CB_PROJECT表的关联字段PRJ_SEQ字段上的索引，与此时的驱动表cb_problem表PRJ_SEQ字段上是否有索引无关。

也就是说，原结论创建两个字段上的索引，其实只需要一个字段就够了，增加一个字段也不会提高索引在这个SQL中的选择性（有可能在其他SQL同时使用两个字段做谓词条件时是高效的）。

总结：

在哪个表上创建索引，创建怎样的索引才能使SQL执行效率最高，需要彻底搞清楚SQL执行计划。有时问题解决了，可能还是没有理解真正的原因。创建索引不是靠猜测，而是经过仔细分析后得出的结果。

两表关联做nested loops时，驱动表要求结果集（经过谓词条件过滤后）要小，表的谓词条件字段上一般要存在索引（不是关联字段）；被驱动表的关联字段上要存在索引，这是基本常识。

比如下面这种OLTP系统常见的两表关联SQL：

select .... from t1,t2

where t1.object_id = t2.object_id and t1.object_name='T1';

执行计划应该是t1表做驱动表，使用object_name字段上的索引，选出少量记录，t2表做被驱动表，object_id字段（关联字段）上要存在索引。

就这个sql本身而言，t1表的object_id字段上有没有索引是没有关系的。在某个客户现场发现这样的SQL，创建的是t1表object_id和object_name两个字段的联合索引，那就大错特错了。

后话：

优化后的执行计划，根据显示的估值行数，驱动表的行数还是比较高，前两个表到底是做nested loops 还是做hash join好一些还不好说（没有测试过），如果是hash join更好一些，那么上面那个索引其实也没有创建的必要了。