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

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

1.disjunctive subquery unnest

disjunctive subquery unnest条件，11g支持，由隐含参数_optimizer_unnest_disjunctive_subq控制:
前提必须是：
1）能unnest subquery必须or分支里的主表关联列是同一个列（如果不是关联列，or在子查询外面，且是主表条件，12c可以直接走or expansion subquery),
   如果子查询里有or分支且主表和子查询表关联，则主表条件在子查询里位置必须在同一侧（大前提，列做运算，函数都不行）且要同一个列
2）能够转为UNION ALL的子查询表对应的列必须类型一致，因为UNION ALL要求字段类型一致。


disjunctive subquery查询转换主要有如下形式：
1）or在子查询外面，比如where 【not】exists(suq1) or exp、where 【not】exists(suq1) or 【not】exists(subq2), exists和in一样，子查询分开，可能对应不同表，
2) or在子查询里面，比如where 【not】exists ( condition1 or condition2...),这种同样要满足disjunctive subquery条件才能转换。

针对or在子查询外面的情况：
1.1）一种格式是where exists(suq1) or exp，见or expansion subquery,12C支持,ORACLE碰到OR子查询效率低，三大方法助力性能起飞_PART1

1.2） subq1 or subq2形式，需要子查询对应主表列条件来源于同一个表，而且顺序一致，且子查询表类型一致，
因为这个要将子查询表做UNION ALL然后变为VIEW，然后再与主表关联，如下示例：

select * from a
where exists (select 1 from b where a.object_id = b.object_id)
or exists (select 1 from c where a.object_id=c.object_id);

这个例子如何subq1 or subq2格式，且主表的列是同一个且在子查询里的条件都在左侧。所以可以
查询转换。
执行计划如下所示，可以看到C和B查询后做成UNION ALL视图，并且剔重（按照object_id剔重）：

Execution Plan
----------------------------------------------------------
Plan hash value: 2880773727

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 111 | 1349 (1)| 00:00:17 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 111 | 1349 (1)| 00:00:17 |
| 3 | VIEW | VW_SQ_1 | 153K| 1949K| 612 (1)| 00:00:08 |
| 4 | HASH UNIQUE | | 1 | 749K| 612 (51)| 00:00:08 |
| 5 | UNION- ALL| | | | | |
| 6 | TABLE ACCESS FULL | C | 76791 | 374K| 306 (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL | B | 76791 | 374K| 306 (1)| 00:00:04 |
|* 8 | INDEX RANGE SCAN | IDX1_A | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| A | 1 | 98 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   8 - access("A"."OBJECT_ID"="VW_COL_1")

如果子查询将a.object_id移动到右侧，则不能
disjunctive subquery unnest，不符合主表关联条件在同一侧：

select * from a
where exists(select 1 from b where b.object_id=a.object_id)
or exists (select 1 from c  where a.object_id=c.object_id)

执行计划有FILTER：

Execution Plan
----------------------------------------------------------
Plan hash value: 3747852665

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 20M (1)| 67:15:33 |
|* 1 |  FILTER| | | | | |
| 2 | TABLE ACCESS FULL| A | 76791 | 7349K| 307 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| B | 1 | 5 | 306 (1)| 00:00:04 |
|* 4 | TABLE ACCESS FULL| C | 1 | 5 | 306 (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."OBJECT_ID"=:B1)
              OR  EXISTS (SELECT 0 FROM "C" "C" WHERE "C"."OBJECT_ID"=:B2))
   3 - filter("B"."OBJECT_ID"=:B1)
   4 - filter("C"."OBJECT_ID"=:B1)

如果对应子查询表关联条件列类型不一致，也不行，因为不符合UNION ALL需要列类型一致条件,
比如下面的使用to_char：

select * from a
where exists(select 1 from b where a.object_id = to_char(b.object_id))
or exists (select 1 from c  where a.object_id = c.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 3747852665

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 20M (1)| 67:25:10 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| A | 76791 | 7349K| 307 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| B | 1 | 5 | 307 (1)| 00:00:04 |
|* 4 | TABLE ACCESS FULL| C | 1 | 5 | 306 (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE
              TO_NUMBER(TO_CHAR("B"."OBJECT_ID"))=:B1) OR  EXISTS (SELECT 0 FROM "C"
              "C" WHERE "C"."OBJECT_ID"=:B2))
   3 - filter(TO_NUMBER(TO_CHAR("B"."OBJECT_ID"))=:B1)
   4 - filter("C"."OBJECT_ID"=:B1)

2)or在子查询里,比如 exists (select 1 from b where a.object_id = b.object_id
or a.object_id = b.data_object_id)
这种子查询是一个表，两个条件，可以直接对b按照条件union all,同样也是将子查询OR条件合并
成VIEW，同样要求主表是同一个列，且顺序一致，子查询表OR条件类型一致，如果类型不一致，
直接报错，这个和前面的OR在外面不一样，前面的subq1 or subq2是不能查询转换，这个是报错，
类似启发式查询转换了。
下面的可以unnest:

select * from a
where exists
(select 1 from b where a.object_id = b.object_id or a.object_id = b.data_object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 609371133

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 111 | 1349 (1)| 00:00:17 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 111 | 1349 (1)| 00:00:17 |
| 3 | VIEW | VW_SQ_1 | 153K| 1949K| 612 (1)| 00:00:08 |
| 4 | HASH UNIQUE | | 1 | 524K| 612 (51)| 00:00:08 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS FULL | B | 76791 | 374K| 306 (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL | B | 76791 | 149K| 306 (1)| 00:00:04 |
|* 8 | INDEX RANGE SCAN | IDX1_A | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| A | 1 | 98 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   8 - access("A"."OBJECT_ID"="VW_COL_1")

修改第二个分支a.object_id到右侧，则不能查询转换：

select * from a
where exists
(select 1 from b where a.object_id = b.object_id or b.data_object_id = a.object_id);

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

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 20M (1)| 67:18:55 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| A | 76791 | 7349K| 307 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| B | 2 | 14 | 307 (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE
              "B"."DATA_OBJECT_ID"=:B1 OR "B"."OBJECT_ID"=:B2))
   3 - filter("B"."DATA_OBJECT_ID"=:B1 OR "B"."OBJECT_ID"=:B2)

类型不一致，直接报错：

select * from a
where exists
(select 1 from b where a.object_id = b.object_id or a.object_id = to_char(b.data_object_id));
                                                                  *
ERROR at line 3:
ORA-01790: expression must have same datatype as corresponding expression

2.针对12c之前的or expansion subquery改写

12c之前针对exp or subquery不能or展开，可以用union all改写，利用LNNVL：

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

11g不能展开子查询：

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)

使用UNION ALL+LNNVL改写,
第二个分支加上lnnvl(a.object_id=100)，相当于a.object<>100 or a.object_id is null,也就是
去掉
a.object_id=100的结果，又保留了第二个分支可能存在a.object is null的结果，实际上12c的
子查询or展开就是这样改写的，
如下所示：

select * from a where a.object_id=100
union all
select * from a
where a.object_name in (select b.object_name from b)
and lnnvl(a.object_id=100);

执行计划如下：

Execution Plan
----------------------------------------------------------
Plan hash value: 1449001439

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74395 | 8936K| | 1156 (100)| 00:00:14 |
| 1 | UNION-ALL | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| A | 1 | 98 | | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_A | 1 | | | 1 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI | | 74394 | 8935K| 2800K| 1154 (1)| 00:00:14 |
| 5 | TABLE ACCESS FULL | B | 77370 | 1888K| | 306 (1)| 00:00:04 |
|* 6 | TABLE ACCESS FULL | A | 77379 | 7405K| | 307 (1)| 00:00:04 |
----------------------------------------------------------------------------------------------

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

   3 - access("A"."OBJECT_ID"=100)
   4 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")
   6 - filter(LNNVL("A"."OBJECT_ID"=100))

其他改写技巧，请见下一篇： ORACLE碰到OR子查询效率低，三大方法助力性能起飞_PART3