公众号：杨建荣的学习笔记 · 作者：r4笔记第87天 · 发布：2015-03-26 23:48:19 · 原文链接

今天开发找到我，说有个问题想征求一下我的意见。
问题的大体意思是，对目前环境中的两个表，我们就叫做表a,表b吧，他说根据一个时间字段去判断是否为5天前的记录，但是这个字段不是索引列字段。
想问我是否需要添加索引会能极大地提高性能。
对于这个问题没有肯定的回答，还是取决于数据和表的特性，首先我看了下表a,表b，根据我的印象，这两个表的数据量都不大，简单验证了一下，一个在300万，一个就10万左右。所以从数据的角度来看，走全表扫描也是合理的，不需要再创建一个索引了，因为新增加的索引可能会对现有的一些sql语句造成印象，还需要做一些评估才可以。
其次，这两个表都是核心层的表，这种表的结构式不能轻易修改的，表中的索引，结构都是在很多项目中反复验证通过的。所以也是不建议直接增加索引的。
开发的同事得到了希望的答案，过一会我就收到了另外一封邮件。
我一看就发现和开发的同事问我的问题有些关联。
这一段脚本是做一个清理相关的操作。最后还尝试用到了分段提交。

DECLARE
CNT_REC NUMBER(13);
tot_rec number(13);
BEGIN
CNT_REC := 0;
tot_rec := 0;

FOR C1 IN (select *from bpm_context_inst
where objid in (select context_inst2context_inst from bpm_proc_inst where objid in
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and END_TREATMENT_DATE <= SYSDATE -1 )))
LOOP
BEGIN
delete from bpm_context_inst t where t.objid = c1.objid;
END;
CNT_REC := CNT_REC + 1;
tot_rec := tot_rec + 1;

IF CNT_REC = 100 THEN
COMMIT;
CNT_REC := 0;
END IF;

END LOOP;
COMMIT;

END;

这段脚本让我有些纠结，因为从这个开发同事的角度来看，这段pl/sql脚本中一旦确定了coll_entity_history
不需要做索引扫描，可以走全表扫描，他们认为就万事大吉了。但是我一看到上面的cursor中的那段代码，就开始担心了。
因为他问我的问题似乎还是和这个问题有一定的差距。表bpm_context_inst，bpm_proc_inst这两个表都是数据量近亿的大表。如果从coll_entity_history中得到了太多的数据，外层关联两个大表，相比性能也会好不到哪去。如果数据量比较大，直接通过cursor得到缓存数据集，做delete还是有一定的性能瓶颈的。对此我也是心有余悸。可以参见我之前碰到的一个问题。http://blog.itpub.net/23718752/viewspace-1172818/
如果数据量较大，可以尝试调优一些这段pl/sql。为了能够准确地评估这段Pl/sql的执行情况，我申请从备份库中导出了这几个表的dump,在测试库中进行测试。
导出很顺利，但是导入的时候表bpm_context_inst是相当慢，等了半个小时多，还是没有什么反应。
以下是通过imp导入的一些数据，执行了15分钟的时候，值导入了60多万行，按照这个进度，今天是不用干别的了。
SQL> select 620540/15/60 from dual;
620540/15/60
————
689.488889
按照之前的测试impdp效率也好不到哪去，为什么呢，我一查看表结构恍然大悟，原来这个表中含有clob字段。
好了想模拟测试数据，看来按照目前的情况还是不现实的了。
我们来显分析一下数据。既然是需要按照每隔5天来做数据的清理，来看看这些天来的数据情况。 以下是我分析得到的近50天来的数据情况，每天的相关数据量其实还是很小的。

按照这个数据量，原本存在性能隐患的那段pl/sql看起来也顺眼多了。但是细细看来，还是有个硬伤。
就是cursor定义的部分，根据pl/sql的实现目标，没有用到clob字段，所以是不相关的。可以在cursor的部分直接过滤掉。
C1 IN (select *from bpm_context_inst
where objid in (select context_inst2context_inst from bpm_proc_inst where objid in
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and END_TREATMENT_DATE <= SYSDATE -1 )))
直接改为select objid from bpm_context_inst就可以了
最后我给出了两种意见，第一种是上面的pl/sql完全可以通过一句delete语句来完成，至于他们关注的分段提交，其实在这个场景中，影响是忽略不计，实际上一次提交性能还要好于分批提交。
即
delete bpm_context_inst
where objid in (select context_inst2context_inst from bpm_proc_inst where objid in
(select /*+ PARALLEL(coll_entity_history 4) */ proc_inst_id from coll_entity_history where END_TREATMENT_DATE >= SYSDATE -5 and END_TREATMENT_DATE <= SYSDATE -1 ))
另外一种就是把cursor定义的部分做一些改动，去除clob字段。对于cursor的提升是还是很大的。
对于这两种意见，开发同事说自己确实考虑得不够周到，不过为了避免给客户造成更多的困扰，还是选用第二种方案。
这个问题带给我的启示就是可能大家问你的问题是一个场景，但是和另外一个场景联系起来，原来的一些推论就不是那么肯定了。就比如这个问题中，开发确定不需要索引对于查询这个表性能影响不大，但是并不意味着在和其他的大表关联时没有问题。如果那种情况发生，还需要做一些额外的优化工作。根据数据量最后得知，变更的数据量很小，所以这个也算是化险为夷吧。