oracle 逻辑优化,提升高度，综合SQL上下文进行逻辑优化

之前我们的优化都是针对单个SQL的优化，但是很多时候SQL是出现在存储过程中，有上下文语境。如果我们能够通过分析业务逻辑，对某段SQL做一个整体优化，那样带来的效果可能会更加显著。

我们在对一个生产系统的AWR报告中逐个TOP SQL进行分析的时候，发现TOP4 的存储过程，包含了TOP9和TOP23，两个SQL的执行时间都比较长(平均执行时间分别是442秒和171秒)，一般SQL执行时间越长越容易进行优化：

截取的TOP4存储过程中的关键代码段：

两个SQL都非常简单，TP_INFOCHECK_RESULT表有9300多万条记录，其中DEAL_TAG字段有4个唯一值，很明显没有索引。

分析与优化建议：

TOP23的优化：

根据TOP23的update语句，虽然DEAL_TAG字段的唯一值少，而且DEAL_TAG='T'的记录数还可能非常多，但是由于每次只取4000条(RONUM<4001)，这个字段上明显需要创建一个索引。

如果没有索引，当DEAL_TAG大部分记录为T的时候，全表扫描只需要扫描一部分block，得到满足条件的4000条件记录就停止了，性能不会差；但是随着不断的update，DEAL_TAG=‘T’的记录越来越少，基本上就相当于要对这个9300万的表做全表扫描了。

预计创建索引后这个update sql的平均执行时间应该1秒左右。

TOP9的优化：

再来看top9，没有DEAL_TAG字段的索引时，每次都要做全表扫描，每次执行需要442秒。即使创建了索引，在DEAL_TAG记录数多的时候，也需要扫描很多的索引block，才能计算出=‘T’的记录总数。

结合存储过程的上下文，做count(*)只是为了判断记录数是否为0，那么我们就可以把rownum=1 作为一个新增的谓词条件加到SQL里面，虽然SQL的逻辑改变了，但是对于整个存储过程的逻辑是没有变化的。

即：select count(*) INTO v_cnt FROM TP_INFOCHECK_RESULT where deal_tag='T' and rownum=1;

加了rownum=1后，这个select count(*)的SQL应该只需要1毫秒左右就能完成。这个SQL就会从原来的top9 ，变成一个基本不消耗资源可以忽略不计的SQL了。

总结：

索引的使用仍是本案例的最大功臣，但是如果我们再仔细对存储过程上下文进行综合分析，还是有可能发现一些逻辑上可以优化的部分，还可以进一步的节约系统资源。类似判断记录是否存在的业务逻辑应该还是比较常见的，如果是不求具体值，只问有没有，那么就可以通过本文的方法进行处理。优化前，这两个SQL使用了大量的CPU和IO资源，而优化后，这些资源的使用基本上可以忽略不计了(当然，新增的索引还是会在表做DML操作时多一步索引维护动作)。

很多时候，系统不是资源不够，而是资源没有合理的使用。如果SQL没有优化，随着表的记录数逐步增加，性能会越来越差。而使用了索引，记录数的增加对性能的影响微乎其微。这就是优化起到的作用！

公众号留言功能已开通，欢迎大家拍砖！