踩坑CBO，解决那些坑爹的SQL优化问题_PART3

接PART2：

2.1.2 OR子查询中的FILTER

  再来看下常见的OR与子查询连用情况，在实际优化过程中，遇到OR与子查询连用，一般都不能unnest subquery了，可能会导致严重性能问题，OR与子查询连用有两种可能：

1）condition or subquery

2）subquery内部包含or,如in (select … from tab where condition1 or condition 2)
还是通过一个具体案例，分享下对于OR子查询优化的处理方式，在某库11g R2中碰到如下SQL，几个小时都没有执行完：

先来看下执行计划：

怎么通过看到这个执行计划，一眼定位性能慢的原因呢？主要通过下列几点来分析定位：

1）执行计划中的Rows，也就是每个步骤返回的cardinality很少，都是几行，在分析表也不是太大，那么怎么可能导致运行几个小时都执行不完呢？执行时间与执行计划关键指标不匹配。很大原因可能就在于统计信息不准，导致CBO优化器估算错误，错误的统计信息导致错误的执行计划，这是第一点。

2）看ID=15到18部分，它们是ID=1 FILTER操作的第二子节点，第一子节点是ID=2部分，很显然，如果ID=2部分估算的cardinality错误，实际情况很大的话，那么对ID=15到18部分四个表全扫描次数将会巨大，那么也就导致灾难产生。

3）很显然，ID=2部分的一堆NESTED LOOPS也是很可疑的，找到ID=2操作的入口在ID=6部分，全表扫描DEALREC_ERR_201608，估算返回1行，很显然，这是导致NESTED LOOPS操作的根源，因此，需要检验其准确性。

  主表DEALREC_ERR_201608在ID=6查询条件中经查要返回2000w行，计划中估算只有1行，因此，会导致NESTED LOOPS次数实际执行千万次，导致效率低下，应该走HASH JOIN，需要更新统计信息。
  另外ID=1是FILTER,它的子节点是ID=2和ID=15、16、17、18，同样的ID 15-18也被驱动千万次。
  找出问题根源后，逐步解决。首先要解决ID=6部分针对DEALREC_ERR_201608表按照查询条件substr(other_class, 1, 3) NOT IN (‘147’,‘151’, …)获得的cardinality的准确性，也就是要收集统计信息。

  然而发现使用size auto,size repeat,对other_class收集直方图均无效果，执行计划中对other_class的查询条件返回行估算还是1（实际2000w行）。

再次执行后的执行计划如下：

1）DEALREC_ERR_201608与B_DEALING_DONE_TYPE原来走NL的现在正确走HASH JOIN。Build table是小结果集，probe table是ERR表大结果集，正确。

2）但是ID=2与ID=11到14，也就是与TMI_NO_INFOS的OR子查询，还是FILTER,驱动数千万次子节点查询，下一步优化要解决的问题。

3）性能从12小时到2小时。

现在要解决的就是FILTER问题，对子查询有OR条件的，简单条件如果能够查询转换，一般会转为一个union all view后再进行semi join、anti join（转换成union all view,如果谓词类型不同，则SQL可能会报错）。对于这种复杂的，优化器就无法查询转换了，因此，改写是唯一可行的方法。分析SQL，原来查询的是同一张表，而且条件类似，只是取的长度不同，那么就好办了！

如何让带OR的子查询执行计划从FILTER变成JOIN。两种方法：

1）改为UNION ALL/UNION

2）语义改写.前面已经使用语义改写，内部转为了类似UNION的操作，如果要继续减少表的访问，则只能彻改写OR条件，避免转换为UNION操作。

再来分析下原始OR条件：

上面含义是ERR表的TMISID截取前8,9,10,11位与TMI_NO_INFOS.BILLID_HEAD匹配，对应匹配BILLID_HEAD长度正好为8,9,10,11。很显然，语义上可以这样改写：

ERR表与TMI_NO_INFOS表关联，ERR.TMISID前8位与ITMI_NO_INFOS.BILLID_HEAD长度在8-11之间的前8位完全匹配，在此前提下，TMISID like BILLID_HEAD ||’%’。

现在就动手彻底改变多个OR子查询，让SQL更加精简，效率更高。改写如下：

执行计划如下：

1）现在的执行计划终于变的更短，更易读，通过逻辑改写走了HASH JOIN，最终一条返回300多万行数据的SQL原先需要12小时运行的SQL，现在3分钟就执行完了。

2）思考：结构良好，语义清晰的SQL编写，有助于优化器选择更合理的执行计划，所以说，写好SQL也是门技术活。

通过这个案例，希望能给大家一些启发，写SQL如何能够自己充当查询转换器，编写的SQL能够减少表、索引、分区等的访问，能够让ORACLE更易使用一些高效算法进行运算，从而提高SQL执行效率。

其实，OR子查询也不一定就完全不能unnest，只是绝大多数情况下无法unnest而已，请看下例：

不可unnest的查询：

可以unnest的查询：

  这2条SQL的差别也就是将条件or id3=id2+1000转换成or id3-1000=id2，前者不可以unnest,后者可以unnest，通过分析10053可以得知：

不可unnest的出现：

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.

Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block SEL$1 (#1).

SU:   Checking validity of unnesting subquery SEL$2 (#2)

SU:     SU bypassed: Invalid correlated predicates.
SU:   Validity checks failed.

可以unnest的出现：

并且将SQL改写为：

  最终CBO先查询T3条件，做个UNION ALL视图，之后与T2关联。从这里来看，对于OR子查询的unnest要求比较严格，从这条语句分析，ORACLE可进行unnest必须要求对主表列不要进行运算操作，优化器自身并未将+1000条件左移，正因为严格，所以大部分情况下，OR子查询也就无法进行unnest了，从而导致各种性能问题。

未完待续，见PART4：