oracle碰到or子查询效率低，三大方法助力性能起飞-凯发app官方网站

子查询的subquery unnest是数据库一种重要的查询转换技术，oracle作为有着强大优化器（cbo)的数据库，同样在子查询转换方面有很多技术，当然限制也较多，本文详细讲解子查询里有or的查询转换技术，利用oracle cbo自身的两大查询转换技术以及手动改写方法，使用三大方法，助力or子查询性能起飞。

先看建表语句：

子查询中有or经常不能unnest subquery，从而走filter执行计划，效率低下，如何unnest,主要有如下三大方法：
1）or expansion subquery(12c支持）
2）disjunective subquery unnest（oracle 11g开始支持）
3) 手动改写成union all，主要利用lnnvl或rowid，以及集合运算规则not(a or b) => not a and not b,not a or not b => not (a and b)。集合改写主要是针对anti join子查询。


oracle针对特定的or subquery,有or expansion subquery(12c开始支持带子查询的or expansion)、disjunctive subquery unnest两种。

1）12c开始对于带子查询的or expansion转换,主要针对exp or subq,对应的exp是主表查询条件，貌似exp条件要走索引，or expansion基于cbqt。

2）oracle cbo有_optimizer_unnest_disjunctive_subq功能（11g)，符合条件的可以进行unnest subquery,
优化器将子查询里的或子查询外的or条件对应的表（非主表）改写成union all，然后按照关联键剔重，做成view，然后与主表关联，这样避免不能unnest走filter，
从而提高效率。

能够进行unnest disjunctive subquery的，要符合一定的条件，主要是针对subq1 or subq2或suq(condition1 or condition2)格式：

2.1）or子查询分支里的主表关联条件必须是同一个列（对exp or subq走or扩展的没有要求）
2.2）or子查询分支里的主表关联条件必须是在同一侧，子查询表对应的关联条件列必须类型一致（因为要改成union all视图，否则报错）。


什么叫disjunctive subquery？
disjunctive subquery指的是子查询里面or条件或子查询外面有or条件，且or条件不能直接从主表或子查询表里获取结果
(非简单过滤条件，简单过滤条件直接可以查了，也就不会造成子查询无法unnest）。
一般情况下，disjunctive subquery是不能unnest的，则执行计划由主表驱动子查询走filter。
符合oracle cbo的unnest disjunctive subquery才可以查询转换，符合条件的会自动将子查询改写为union all。
或者能够走or展开的走or展开。


对于or简单条件，只是过滤的，不影响subquery unnest:
如果or条件来源于同一张表，且可以直接获取结果（与其他条件是and连接或能转为and），这种相当于普通谓词，不是disjunctive subquery,不影响subquery unnest。
比如：


这里的子查询里or条件是(b.object_id >100 or b.created>trunc(sysdate-10)而且与前面的条件是and连接，所以对于子查询表b可以直接按or条件过滤后
与主表关联，可以直接subquery unnest。
执行计划如下：
可以看到这种来源于同一个表的or条件，而且与其他条件是and连接，可以unnest subquery,执行计划走hash join right semi


同样的，下面的or条件全部来源于主表，可以直接查询，也不影响unnest：




执行计划如下：


下面针对or的subquery查询转换，分别研究下。
1.or expansion subquery
or expansion subquery转换条件：
主要是where exists(suq1) or exp结构，其中exp是主表条件，这种从12c开始可以直接走or expansion，由隐含参数_optimizer_cbqt_or_expansion控制，基于cbqt的查询转换。
貌似主表的exp条件走索引才能完美查询转换，否则另一个or分支还会有filter。


下面是19c演示的or expansion子查询转换：
可以直接or展开，然后lnnvl(a.object_id=100)去掉重复行，lnnvl相当于a.object_id<>100 or a.object_id is null。
从执行计划可以看到，or的两个分支分别用union all连接，对于or子查询的分支也能unnest subquery,走hash join right semi。


但是如果去掉a表object_id列索引：虽然也能or展开，但是子查询不能unnest,使用unnest hints也无效，所以针对or expansion的主表单独的条件要走索引，
这些是基于cbqt的，可能主表单独的分支走全表扫描，cost过大，子查询就不展开了。