OR 有时候不能很好的选择执行计划，
改成union
NVL
文中重要总结
大结果集用hash join --> 改写为表关联
inner join 还是 left join？ --> left join，因为有or条件，必须保证 object_name LIKE 'W%' 部分的条件不被关联条件过滤掉（这跟单纯的半连接改写是不一样的）。因此t_do表必须是主表，关联后返回其所有数据。
如何实现exists条件 --> 关联后t2.object_id is not null，就是关联后有匹配上的（ t_do和t2表中均存在的）
半关联自带去重 --> t2表的子查询要加 distinct
一、 同一字段谓词条件的or
1. 构造测试表
create table t_do as select * from dba_objects;
create index t_do_n1 on t_do(object_name);
create index t_do_n2 on t_do(owner);
create table t_dt as select * from dba_tables;
create index t_dt_n1 on t_dt(table_name);
create table t_di as select * from dba_indexes;
create index t_di_n1 on t_di(index_name);
2. 原SQL
SELECT object_type,object_name
FROM t_do
WHERE owner = 'SYS'
AND
( object_name = 'T3'
OR object_name IN (SELECT table_name FROM t_dt WHERE table_name LIKE 'TRIGER%')
OR object_name IN (SELECT index_name FROM t_di WHERE index_name LIKE 'IND_OBJ%')
)
order by 1;
由于or的写法，t3表虽然object_name字段有索引，选择率也高，但用不了。
------19 C 已经是三个的UNION ALL
Plan Hash Value : 3194004644
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 25 | 00:00:01 |
| 1 | SORT ORDER BY | | 8 | 632 | 25 | 00:00:01 |
| 2 | VIEW | VW_ORE_75184356 | 8 | 632 | 24 | 00:00:01 |
| 3 | UNION-ALL | | | | | |
| * 4 | TABLE ACCESS BY INDEX ROWID BATCHED | T_DO | 6 | 870 | 13 | 00:00:01 |
| * 5 | INDEX RANGE SCAN | T_DO_N1 | 16 | | 3 | 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 211 | 5 | 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 211 | 5 | 00:00:01 |
| 8 | SORT UNIQUE | | 1 | 66 | 1 | 00:00:01 |
| * 9 | INDEX RANGE SCAN | T_DT_N1 | 1 | 66 | 1 | 00:00:01 |
| * 10 | INDEX RANGE SCAN | T_DO_N1 | 1 | | 2 | 00:00:01 |
| * 11 | TABLE ACCESS BY INDEX ROWID | T_DO | 1 | 145 | 3 | 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 211 | 5 | 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 211 | 5 | 00:00:01 |
| 14 | SORT UNIQUE | | 1 | 66 | 1 | 00:00:01 |
| * 15 | INDEX RANGE SCAN | T_DI_N1 | 1 | 66 | 1 | 00:00:01 |
| * 16 | INDEX RANGE SCAN | T_DO_N1 | 1 | | 2 | 00:00:01 |
| * 17 | INDEX RANGE SCAN | T_DT_N1 | 1 | 66 | 1 | 00:00:01 |
| * 18 | TABLE ACCESS BY INDEX ROWID | T_DO | 1 | 145 | 3 | 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("OWNER"='SYS')
* 5 - access("OBJECT_NAME"='T3')
* 9 - access("TABLE_NAME" LIKE 'TRIGER%')
* 9 - filter("TABLE_NAME" LIKE 'TRIGER%')
* 10 - access("OBJECT_NAME"="TABLE_NAME")
* 10 - filter("OBJECT_NAME" LIKE 'TRIGER%' AND LNNVL("OBJECT_NAME"='T3'))
* 11 - filter("OWNER"='SYS')
* 15 - access("INDEX_NAME" LIKE 'IND_OBJ%')
* 15 - filter("INDEX_NAME" LIKE 'IND_OBJ%')
* 16 - access("OBJECT_NAME"="INDEX_NAME")
* 16 - filter("OBJECT_NAME" LIKE 'IND_OBJ%' AND LNNVL("OBJECT_NAME"='T3') AND LNNVL( EXISTS (SELECT 0 FROM "T_DT" "T_DT" WHERE "TABLE_NAME"=:B1 AND "TABLE_NAME" LIKE 'TRIGER%')))
* 17 - access("TABLE_NAME"=:B1)
* 17 - filter("TABLE_NAME" LIKE 'TRIGER%')
* 18 - filter("OWNER"='SYS')
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
- This is an adaptive plan
3. 改写方法
对于同一个字段多个or条件的，可以直接改为 字段 in (select xxx union all select xxx)
SELECT object_type,object_name
FROM t_do
WHERE owner = 'SYS'
and object_name in
(
select 'T3' from dual
union all
SELECT table_name FROM t_dt WHERE table_name LIKE 'TRIGER%'
union all
SELECT index_name FROM t_di WHERE index_name LIKE 'IND_OBJ%'
)
order by 1;
---------------------in 内改写 ----------------
Plan Hash Value : 56913885
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2321 | 23 | 00:00:01 |
| 1 | SORT ORDER BY | | 11 | 2321 | 23 | 00:00:01 |
| 2 | NESTED LOOPS | | 11 | 2321 | 22 | 00:00:01 |
| 3 | NESTED LOOPS | | 15 | 2321 | 22 | 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 3 | 198 | 4 | 00:00:01 |
| 5 | HASH UNIQUE | | 3 | 132 | 4 | 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| * 8 | INDEX RANGE SCAN | T_DT_N1 | 1 | 66 | 1 | 00:00:01 |
| * 9 | INDEX RANGE SCAN | T_DI_N1 | 1 | 66 | 1 | 00:00:01 |
| * 10 | INDEX RANGE SCAN | T_DO_N1 | 5 | | 2 | 00:00:01 |
| * 11 | TABLE ACCESS BY INDEX ROWID | T_DO | 4 | 580 | 6 | 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 8 - access("TABLE_NAME" LIKE 'TRIGER%')
* 8 - filter("TABLE_NAME" LIKE 'TRIGER%')
* 9 - access("INDEX_NAME" LIKE 'IND_OBJ%')
* 9 - filter("INDEX_NAME" LIKE 'IND_OBJ%')
* 10 - access("OBJECT_NAME"="'T3'")
* 11 - filter("OWNER"='SYS')
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
- This is an adaptive plan
4. 补充
12.2版本中对 or 进行了加强，可以尝试添加 /*+ or_expand */ hint来优化SQL。
SELECT /*+ or_expand */ object_type,object_name
FROM t_do
WHERE owner = 'SYS'
AND
( object_name = 'T3'
OR object_name IN (SELECT table_name FROM t_dt WHERE table_name LIKE 'TRIGER%')
OR object_name IN (SELECT index_name FROM t_di WHERE index_name LIKE 'IND_OBJ%')
)
order by 1;
----------19才下 同不该写一样
从执行计划可以看到，它是把三部分分别做关联后再union all，明显复杂很多，效果没有改写的好。
二、 两个字段谓词条件的or
select object_name,object_type,object_id from t_do where object_name='I_TS1' or object_id<=10;
Plan Hash Value : 258317532
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1472 | 387 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | T_DO | 16 | 1472 | 387 | 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("OBJECT_ID"<=10 OR "OBJECT_NAME"='I_TS1')
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
优化方法：如果字段选择率高，两个字段分别加索引即可
三、 高选择度的or改写
上面这种情况，如果两个字段的选择性可以，而且都存在索引，不论是oracle还是mysql，优化器都是会自动改写的。如果要手工改写，可以这样改：
select object_name,object_type,object_id from t_do where object_name='T_DO'
union all
select object_name,object_type,object_id from t_do where object_id<=10 and lnnvl(object_name='T_DO') ;
Plan Hash Value : 2703103268
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 1564 | 389 | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | T_DO | 1 | 92 | 2 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | T_DO_N1 | 1 | | 2 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | T_DO | 16 | 1472 | 387 | 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("OBJECT_NAME"='T_DO')
* 4 - filter("OBJECT_ID"<=10 AND LNNVL("OBJECT_NAME"='T_DO'))
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
等价改写是用union all，而不是union，既不等价，效率又低
这里跟 第一部分“同一字段谓词条件的or” 的区别是：or的两边是两个不同的字段，两个条件数据可能会有交集，因此不能只用 union all，还需要加 lnnvl(object_name='T_DO') 去除查询中的重复值。注意也不要写成object_name<>'T_DO'，两种不等价。
lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。非oracle库没有lnnvl函数，其实 lnnvl(object_name='T_DO') 就相当于(object_name<>'T_DO' or object_name is null)。
四、 or与半连接 in/exists
select object_name,object_type,object_id from t_do where object_name='T_DO'
or exists (select 1 from t_do t2 where t_do.object_id=t2.object_id and t2.object_id<=10);
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 112 (100)| | 9 |00:00:00.01 | 424 |
|* 1 | FILTER | | 1 | | | | | 9 |00:00:00.01 | 424 |
| 2 | TABLE ACCESS FULL| t_do | 1 | 23754 | 2134K| 112 (1)| 00:00:01 | 22943 |00:00:00.01 | 410 |
|* 3 | FILTER | | 22943 | | | | | 9 |00:00:00.01 | 14 |
|* 4 | INDEX RANGE SCAN| IND_T2_N2 | 9 | 1 | 13 | 1 (0)| 00:00:01 | 9 |00:00:00.01 | 14 |
--------------------------------------------------------------------------------------------------------------------------
这种情况优化器就不会自动帮你改写了，需要手动改写（12.2版本及以上可以自动使用or_expand做查询转换)。
本质上改写的思想还是：小结果用nest loop，大结果集用hash join。
1. 小结果集的改写
object_name='T_DO' 结果集小，还是按照上面的思路：
select object_name,object_type,object_id from t_do where object_name='T_DO'
union all
select object_name,object_type,object_id from t_do where exists (select 1 from t_do t2 where t_do.object_id=t2.object_id and t2.object_id<=10) and lnnvl(object_name='T_DO');
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 9 |00:00:00.01 | 15 | 1 |
| 1 | UNION-ALL | | 1 | | | | 9 |00:00:00.01 | 15 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| t_do | 1 | 1 | 92 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | 0 |
|* 3 | INDEX RANGE SCAN | IND_t_do_N1 | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | 0 |
| 4 | NESTED LOOPS | | 1 | 1 | 105 | 5 (20)| 00:00:01 | 9 |00:00:00.01 | 13 | 1 |
| 5 | NESTED LOOPS | | 1 | 1 | 105 | 5 (20)| 00:00:01 | 9 |00:00:00.01 | 11 | 1 |
| 6 | VIEW | VW_SQ_1 | 1 | 9 | 117 | 2 (0)| 00:00:01 | 9 |00:00:00.01 | 2 | 0 |
| 7 | HASH UNIQUE | | 1 | 1 | 117 | | 9 |00:00:00.01 | 2 | 0 |
|* 8 | INDEX RANGE SCAN | IND_T2_N2 | 1 | 9 | 117 | 2 (0)| 00:00:01 | 9 |00:00:00.01 | 2 | 0 |
|* 9 | INDEX RANGE SCAN | IND_t_do_N2 | 9 | 1 | | 1 (0)| 00:00:01 | 9 |00:00:00.01 | 9 | 1 |
|* 10 | TABLE ACCESS BY INDEX ROWID | t_do | 9 | 1 | 92 | 2 (0)| 00:00:01 | 9 |00:00:00.01 | 2 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan Hash Value : 4140927447
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1667 | 776 | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | T_DO | 1 | 92 | 2 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | T_DO_N1 | 1 | | 2 | 00:00:01 |
| * 4 | HASH JOIN SEMI | | 15 | 1575 | 774 | 00:00:01 |
| * 5 | TABLE ACCESS FULL | T_DO | 16 | 1472 | 387 | 00:00:01 |
| * 6 | TABLE ACCESS FULL | T_DO | 16 | 208 | 387 | 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("OBJECT_NAME"='T_DO')
* 4 - access("T_DO"."OBJECT_ID"="T2"."OBJECT_ID")
* 5 - filter("T_DO"."OBJECT_ID"<=10 AND LNNVL("OBJECT_NAME"='T_DO'))
* 6 - filter("T2"."OBJECT_ID"<=10)
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
2. 大结果集的改写
object_name='T_DO' 结果集大
原SQL
select count(1) from t_do where object_name LIKE 'W%' or exists (select 1 from t_do t2 where t_do.object_id=t2.object_id and owner='SYS');
select count(1) from t_do where object_name LIKE 'W%' or exists (select /*+ full(t2) */ 1 from t_do t2 where t_do.object_id=t2.object_id and owner='SYS');
--不同
select count(1) from t_do where object_name LIKE 'W%' or exists (select /*+ full(t2) */ 1 from t_do t2 where t_do.object_id=t2.object_id and t2.owner='SYS1'); --120s
select count(1) from t_do where object_name LIKE 'W%' or exists (select /*+ full(t2) */ 1 from t_do t2 where t_do.object_id=t2.object_id and t_do.owner='SYS1'); --1s
--create index T_DO_N3 on T_DO (OBJECT_NAME, OWNER) 效果不大------------------
COUNT(OWNER)
------------
14866
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 112 (100)| | 1 |00:00:00.06 | 43900 |
| 1 | SORT AGGREGATE | | 1 | 1 | 145 | | | 1 |00:00:00.06 | 43900 |
|* 2 | FILTER | | 1 | | | | | 14866 |00:00:00.06 | 43900 |
| 3 | TABLE ACCESS FULL | t_do | 1 | 23754 | 3363K| 112 (1)| 00:00:01 | 22943 |00:00:00.01 | 409 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 21528 | 132 | 10428 | 2 (0)| 00:00:01 | 13451 |00:00:00.04 | 43491 |
|* 5 | INDEX RANGE SCAN | IND_T2_N2 | 21528 | 80 | | 1 (0)| 00:00:01 | 21527 |00:00:00.02 | 21964 |
---------------------------------------------------------------------------------------------------------------------------------------------
改写思路
大结果集用hash join --> 改写为表关联
inner join 还是 left join？ --> left join，因为有or条件，必须保证 object_name LIKE 'W%' 部分的条件不被关联条件过滤掉（这跟单纯的半连接改写是不一样的）。因此t_do表必须是主表，关联后返回其所有数据。
如何实现exists条件 --> 关联后t2.object_id is not null，就是关联后有匹配上的（ t_do和t2表中均存在的）
半关联自带去重 --> t2表的子查询要加 distinct
改写后语句
select count(1)
from t_do
left join (select distinct object_id from t_do t2 where owner='SYS') v_t2
on t_do.object_id=v_t2.object_id
where ( t_do.object_name LIKE 'W%' or v_t2.object_id is not null);
select count(*) from (
select * from t_do where object_name LIKE 'W%' union
select * from t_do where
exists (select /*+ full(t2) */ 1 from t_do t2 where t_do.object_id=t2.object_id and owner='SYS'));
---52961
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 474 (100)| | 1 |00:00:00.01 | 840 |
| 1 | SORT AGGREGATE | | 1 | 1 | 158 | | | | 1 |00:00:00.01 | 840 |
|* 2 | FILTER | | 1 | | | | | | 14866 |00:00:00.01 | 840 |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 23754 | 3665K| | 474 (1)| 00:00:01 | 22943 |00:00:00.01 | 840 |
| 4 | VIEW | | 1 | 13210 | 167K| | 362 (1)| 00:00:01 | 14556 |00:00:00.01 | 431 |
| 5 | HASH UNIQUE | | 1 | 13210 | 1019K| 1160K| 362 (1)| 00:00:01 | 14556 |00:00:00.01 | 431 |
|* 6 | TABLE ACCESS FULL | T2 | 1 | 13210 | 1019K| | 118 (1)| 00:00:01 | 14556 |00:00:00.01 | 431 |
| 7 | TABLE ACCESS FULL | t_do | 1 | 23754 | 3363K| | 112 (1)| 00:00:01 | 22943 |00:00:00.01 | 409 |
----------------------------------------------------------------------------------------------------------------------------------
3. 实例验证
下面我们通过一个简化的例子，看看大结果改写中间每一步的结果输出，加深理解。
构造测试表
SQL> create table ta(id int,name varchar2(10),owner varchar2(10));
Table created.
SQL> create table tb(id int,name varchar2(10),owner varchar2(10));
Table created.
insert into ta (ID, NAME, OWNER)
values (1, 'A', 'SYS');
insert into ta (ID, NAME, OWNER)
values (3, 'W', 'SYS');
insert into ta (ID, NAME, OWNER)
values (2, 'B', 'TMP');
insert into tb (ID, NAME, OWNER)
values (1, 'A', 'SYS');
insert into tb (ID, NAME, OWNER)
values (2, 'B', 'TMP');
insert into tb (ID, NAME, OWNER)
values (3, 'W', 'TMP');
insert into tb (ID, NAME, OWNER)
values (4, 'C', 'TMP');
insert into tb (ID, NAME, OWNER)
values (5, 'D', 'SYS');
SQL> select * from ta;
ID NAME OWNER
---------- ---------- ----------
1 A SYS
2 B TMP
3 W SYS
SQL> select * from tb;
ID NAME OWNER
---------- ---------- ----------
1 A SYS
2 B TMP
3 W TMP
4 C TMP
5 D SYS
原SQL
select * from ta where name LIKE 'W%'
or exists (select 1 from tb where ta.id=tb.id and owner='SYS');
ID NAME OWNER
---------- ---------- ----------
1 A SYS
3 W SYS
name LIKE 'W%'：ta表id=3这行一定要保留
tb.owner='SYS'：tb表保留id=1和5
ta.id=tb.id：只有 id=1 符合
最后ta表返回id=1和3的数据
改写为 inner join 会怎么样？
下面就是当做普通的半连接来改写：
SQL> select * from ta join (select distinct id from tb where owner='SYS') v_tb on ta.id=v_tb.id where (ta.name like 'W%' or v_tb.id is not null);
ID NAME OWNER ID
---------- ---------- ---------- ----------
1 A SYS 1
子查询：b表返回 id=1,5
inner join：on优先级比where高，按照ta.id=v_tb.id ，a表一下只剩id=1（其实就错在这步，ta有or条件，应该作为主表，不论能不能匹配到tb，都应该返回所有数据）
where 条件：匹配到 v_tb.id is not null 该行返回；没有 ta.name like 'W%' 的行了，忽略
改写为 left join 会怎么样？
首先我们不加 v_tb.id is not null，可以看到 根据left join含义 ta表的所有行都被保留了下来。但是 id=2 这一行，既不符合tb表中存在，又不符合 ta.name like 'W%'，所以应该是要过滤掉的。
SQL> select * from ta left join (select distinct id from tb where owner='SYS') v_tb on ta.id=v_tb.id;
ID NAME OWNER ID
---------- ---------- ---------- ----------
1 A SYS 1
2 B TMP
3 W SYS
增加条件 v_tb.id is not null 仅保留tb匹配行，最终返回结果与原sql一致
SQL> select * from ta left join (select distinct id from tb where owner='SYS') v_tb on ta.id=v_tb.id where (ta.name like 'W%' or v_tb.id is not null);
ID NAME OWNER ID
---------- ---------- ---------- ----------
1 A SYS 1
3 W SYS
五、 update语句中的or
原SQL是这样的，expiretime字段有索引，但OR的写法导致无法用到，绝大部分数据只能在回表时做filter，效率很低。
1. 测试案例
我们做一个简化版的例子：
create table t3(id int,name varchar2(20),state int);
insert into t3 values(1,'aaa',0);
insert into t3 values(2,'bbb',0);
insert into t3 values(3,'ccc',0);
commit;
create index ind_t3_id on t3(id);
select * from t3;
测试update
update t3 set id=id+10 where id=1 or (id>=10 and state=0);
查看更新后结果
2. 直接但不等价的改写
根据前面select or的改写，最直接的想法就是把它改成union all + lnnvl形式，又因为update没有union all的语法，这里拆成了2个update。
update t3 set id=id+10 where id=1
-- union all
update t3 set id=id+10 where (id>=10 and state=0) and lnnvl(id=1);
这种写法在把id更新为固定值（比如100）时没有问题，但如果像上面更新成id+10或者时间字段更新成sysdate，第二条update就可能会重复更新数据。
3. 等价改写
可以改写为merge语句，把union all + lnnvl 格式语句放在using部分，比如这样
merge into t3 b
using (
select * from t3 where id=1
union all
select * from t3 where (id>=10 and state=0) and lnnvl(id=1)
) a
on (b.id=a.id)
when matched then
update set b.id = b.id+10;
但是一执行发现会报错
我们在之前的文章 Oracle 关联更新 update_Hehuyi_In的博客-CSDN博客 中学习过这种“更新关联字段的merge写法”，需要将关联字段改为rowid。这里因为只涉及t3一个表，直接在using部分加上rowid字段即可。
merge into t3 b
using (
select rowid as rid,t3.* from t3 where id=1
union all
select rowid as rid,t3.* from t3 where (id>=10 and state=0) and lnnvl(id=1)
) a
on (b.rowid=a.rid)
when matched then
update set b.id = b.id+10;
六、 关联条件的or改写-------nvl(t3.id,t4.id) 有点意思----------
1. 构造测试表
我们再加两个测试表
create table t4(id int,name varchar2(20),state int);
insert into t4 values(1,'ddd',0);
insert into t4 values(10,'eee',0);
insert into t4 values(20,'fff',0);
commit;
create table t5(id int,name varchar2(20),state int);
insert into t5 values(1,'ggg',0);
insert into t5 values(5,'hhh',0);
insert into t5 values(20,'iii',0);
commit;
2. 关联条件中用or
select t3.id,t4.id,t5.id from t3
full join t4
on t3.id = t4.id
left join t5
on t3.id = t5.id or t4.id = t5.id;
查看执行计划（11g版本），外层只能用nest loop join，全关联返回结果集大时效率低。
3. 改写方法
还是按照之前的思路，结果集大时，通过改写让它走hash join，这里巧妙地把or条件改为了nvl。
select t3.id,t4.id,t5.id from t3
full join t4
on t3.id = t4.id
left join t5
on nvl(t3.id,t4.id) = t5.id;
改写后的SQL走上了hash join
这里补充一下，在19c版本测试时，发现原SQL外层查询走的也不再是nest loop join，而是merge join，应该是优化器又增强了。
471
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
