一、 同一字段谓词条件的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字段有索引，选择率也高，但用不了。
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;
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;
从执行计划可以看到，它是把三部分分别做关联后再union all，明显复杂很多，效果没有改写的好。
二、 两个字段谓词条件的or
select object_name,object_type,object_id from t1 where object_name='T1' or object_id<=10;
优化方法：如果字段选择率高，两个字段分别加索引即可
三、 高选择度的or改写
上面这种情况，如果两个字段的选择性可以，而且都存在索引，不论是oracle还是mysql，优化器都是会自动改写的。如果要手工改写，可以这样改：
select object_name,object_type,object_id from t1 where object_name='T1'
union all
select object_name,object_type,object_id from t1 where object_id<=10 and lnnvl(object_name='T1') ;
等价改写是用union all，而不是union，既不等价，效率又低
这里跟 第一部分“同一字段谓词条件的or” 的区别是：or的两边是两个不同的字段，两个条件数据可能会有交集，因此不能只用 union all，还需要加 lnnvl(object_name='T1') 去除查询中的重复值。注意也不要写成object_name<>'T1'，两种不等价。
lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。非oracle库没有lnnvl函数，其实 lnnvl(object_name='T1') 就相当于(object_name<>'T1' or object_name is null)。
四、 or与半连接 in/exists
select object_name,object_type,object_id from t1 where object_name='T1' or exists (select 1 from t2 where t1.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| T1 | 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='T1' 结果集小，还是按照上面的思路：
select object_name,object_type,object_id from t1 where object_name='T1'
union all
select object_name,object_type,object_id from t1 where exists (select 1 from t2 where t1.object_id=t2.object_id and t2.object_id<=10) and lnnvl(object_name='T1');
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 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| T1 | 1 | 1 | 92 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | 0 |
|* 3 | INDEX RANGE SCAN | IND_T1_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_T1_N2 | 9 | 1 | | 1 (0)| 00:00:01 | 9 |00:00:00.01 | 9 | 1 |
|* 10 | TABLE ACCESS BY INDEX ROWID | T1 | 9 | 1 | 92 | 2 (0)| 00:00:01 | 9 |00:00:00.01 | 2 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------2. 大结果集的改写
object_name='T1' 结果集大
原SQL
select count(owner) from t1 where object_name LIKE 'W%' or exists (select 1 from t2 where t1.object_id=t2.object_id and owner='SYS');
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 | T1 | 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%' 部分的条件不被关联条件过滤掉（这跟单纯的半连接改写是不一样的）。因此t1表必须是主表，关联后返回其所有数据。
- 如何实现exists条件 --> 关联后t2.object_id is not null，就是关联后有匹配上的（ t1和t2表中均存在的）
- 半关联自带去重 --> t2表的子查询要加 distinct
改写后语句
select count(owner)
from t1
left join (select distinct object_id from t2 where owner='SYS') v_t2
on t1.object_id=v_t2.object_id
where ( t1.object_name='EMP' or v_t2.object_id is not null);
SQL> select count(owner)
2 from t1
3 left join (select distinct object_id from t2 where owner='SYS') v_t2
4 on t1.object_id=v_t2.object_id
5 where ( t1.object_name like 'W%' or v_t2.object_id is not null);
COUNT(OWNER)
------------
14866
----------------------------------------------------------------------------------------------------------------------------------
| 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 | T1 | 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.
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改写
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，应该是优化器又增强了。
参考
Oracle 表关联、半关联、反关联_Hehuyi_In的博客-CSDN博客
Oracle 关联更新 update_Hehuyi_In的博客-CSDN博客
《SQL写法与改写-第二期》
1294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
