ORACLE碰到OR子查询效率低，三大方法助力性能起飞_PART1

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

先看建表语句：

DDL:
drop table a;
drop table b;
drop table c;
create table a as select * from dba_objects;
create table b as select * from a;
create table c as select * from a;
create index idx_a on a(object_id);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'a',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'b',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'c',estimate_percent=>10,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);

子查询中有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。
比如：

select * from a
where exists(select 1 from b where a.object_name=b.object_name and
            (b.object_id >100 or b.created>trunc(sysdate-10)))

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

Execution Plan
----------------------------------------------------------
Plan hash value: 2016728674

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76791 | 9M| | 1198 (1)| 00:00:15 |
|* 1 | HASH JOIN RIGHT SEMI| | 76791 | 9M| 3752K| 1198 (1)| 00:00:15 |
|* 2 | TABLE ACCESS FULL | B | 76702 | 2846K| | 307 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | A | 76791 | 7349K| | 307 (1)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
   2 - filter("B"."OBJECT_ID">100 OR "B"."CREATED">TRUNC(SYSDATE@!-10))

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

select * from a where (a.object_id >100 or a.created>trunc(sysdate-10))
   and exists(select 1 from b where a.object_name=b.object_name)

执行计划如下：

Execution Plan
----------------------------------------------------------
Plan hash value: 3112244577

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       | 76702 |  9213K|       |   948   (1)| 00:00:12 |
|*  1 | HASH JOIN RIGHT SEMI |       | 76702 |  9213K|  2776K|   948   (1)| 00:00:12 |
|   2 |   INDEX FAST FULL SCAN| IDX_B | 76791 |  1874K|       |   105   (1)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL   | A     | 76702 |  7340K|       |   307   (1)| 00:00:04 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
3 - filter("A"."OBJECT_ID">100 OR "A"."CREATED">TRUNC(SYSDATE@!-10))

下面针对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子查询转换：

select/*+or_expand*/ * from a
where a.object_name in (select/*+unnest*/ b.object_name from b)
or a.object_id=100 ;

可以直接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。

Execution Plan
----------------------------------------------------------
Plan hash value: 2365798918

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 72150 |    33M|       |  1537   (1)| 00:00:01 |
|   1 |  VIEW                                 | VW_ORE_377C5901 | 72150 |    33M|       |  1537   (1)| 00:00:01 |
|   2 | UNION-ALL |                 |       |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| A               |     1 |   132 |       |     2   (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN  | IDX_A           |     1 |       |       |     1   (0)| 00:00:01 |
|*  5 | HASH JOIN RIGHT SEMI |                 | 72149 |    13M|  5504K|  1535   (1)| 00:00:01 |
|   6 |     VIEW                              | VW_NSO_1        | 72160 |  4650K|       |   386   (1)| 00:00:01 |
|   7 |      TABLE ACCESS FULL                | B               | 72160 |  2466K|       |   386   (1)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL                 | A               | 72149 |  9300K|       |   389   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."OBJECT_ID"=100)
5 - access("A"."OBJECT_NAME"="OBJECT_NAME")
8 - filter(LNNVL("A"."OBJECT_ID"=100))

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

drop index idx_a

执行计划如下，可以通过执行计划看到是子查询分支走FILTER：

Execution Plan
----------------------------------------------------------
Plan hash value: 2886310809

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 | 72150 |    33M|  1160   (1)| 00:00:01 |
|   1 |  VIEW                | VW_ORE_377C5901 | 72150 |    33M|  1160   (1)| 00:00:01 |
|   2 |   UNION-ALL          |                 |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | A               |     1 |   132 |   386   (1)| 00:00:01 |
|*  4 | FILTER |                 |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| A               | 72149 |  9300K|   388   (2)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| B               |     1 |    35 |   386   (1)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("A"."OBJECT_ID"=100)
4 - filter( EXISTS (SELECT /*+ UNNEST */ 0 FROM "B" "B" WHERE
"B"."OBJECT_NAME"=:B1))
5 - filter(LNNVL("A"."OBJECT_ID"=100))
6 - filter("B"."OBJECT_NAME"=:B1)

在11g里无法查询转换，走FILTER：

Execution Plan
----------------------------------------------------------
Plan hash value: 1049914119

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3870 |   370K|   307   (1)| 00:00:04 |
|*  1 | FILTER |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| A    | 77380 |  7405K|   307   (1)| 00:00:04 |
|*  3 |   TABLE ACCESS FULL| B    |     1 |    25 |   166   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."OBJECT_ID"=100 OR  EXISTS (SELECT /*+ UNNEST */ 0
FROM "B" "B" WHERE "B"."OBJECT_NAME"=:B1))
3 - filter("B"."OBJECT_NAME"=:B1)

对于版本是12c之前的或者主表的or分支条件不能走索引的，只能手动改写为UNION ALL，这个后续会写。
下一篇：ORACLE碰到OR子查询效率低，三大方法助力性能起飞_PART2